Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression

On 4th of January 2024, Peter Eisentraut committed patch:

ALTER TABLE command to change generation expression
 
This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION
that changes the generation expression of a generated column.
 
The syntax is not standard but was adapted from other SQL
implementations.
 
This command causes a table rewrite, using the usual ALTER TABLE
mechanisms.  The implementation is similar to and makes use of some of
the infrastructure of the SET DATA TYPE subcommand (for example,
rebuilding constraints and indexes afterwards).  The new command
requires a new pass in AlterTablePass, and the ADD COLUMN pass had to
be moved earlier so that combinations of ADD COLUMN and SET EXPRESSION
can work.
 
Author: Amul Sul <sulamul@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b94yyJeGA-5M951_Lr+KfZokOp-2kXicpmEhi5FXhBeTog@mail.gmail.com

So, I have a bit mixed feelings about it.

First the good thing: it's great to have the ability to change the expression, and while it will require table rewrite, and long standing lock – I understand why it's needed.

Let's see how that works:

=$ CREATE TABLE testing (
    id int8 generated always AS IDENTITY PRIMARY KEY,
    when_ts TIMESTAMP,
    when_year int4 generated always AS ( EXTRACT(YEAR FROM when_ts)::int4 ) stored
);
CREATE TABLE
 
=$ INSERT INTO testing (when_ts) SELECT now() - random() * '10 years'::INTERVAL FROM generate_series(1,5) i;
INSERT 0 5
 
=$ SELECT * FROM testing;
 id |          when_ts           | when_year 
----+----------------------------+-----------
  1 | 2019-05-29 03:21:02.964805 |      2019
  2 | 2018-03-25 16:52:43.707205 |      2018
  3 | 2022-04-22 17:57:53.652805 |      2022
  4 | 2015-03-14 21:11:22.875205 |      2015
  5 | 2020-02-10 11:27:18.171205 |      2020
(5 ROWS)

with playground set, I can now change the generated expression:

=$ ALTER TABLE testing ALTER COLUMN when_year SET expression AS ( EXTRACT(YEAR FROM when_ts)::int4 - 2000 );
ALTER TABLE
 
=$ SELECT * FROM testing;
 id |          when_ts           | when_year 
----+----------------------------+-----------
  1 | 2019-05-29 03:21:02.964805 |        19
  2 | 2018-03-25 16:52:43.707205 |        18
  3 | 2022-04-22 17:57:53.652805 |        22
  4 | 2015-03-14 21:11:22.875205 |        15
  5 | 2020-02-10 11:27:18.171205 |        20
(5 ROWS)

Obviously, this is great.

So why do I have mixed feelings?

Let me show you:

=$ psql -c '\h alter table' | grep -i expression
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name SET EXPRESSION AS ( expression )
    ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
  CHECK ( expression ) [ NO INHERIT ] |
{ CHECK ( expression ) [ NO INHERIT ] |
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

so, we have “set data type x using expression", “set default expression", and “set expression as ()". SQL is always very verbose, but I think that in this case it could used one more word. Like: set generated expression as, perhaps?

Anyway – functionality is great. And I'd like to thank everyone involved.

=$
|

Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression

One thought on “Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression”

Leave a Reply