Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

Title: Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

On 14th of January 2020, Peter Eisentraut committed patch:

ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION 
 
Add an ALTER TABLE subcommand for dropping the generated property from
a column, per SQL standard.
 
Discussion: https://www.postgresql.org/message-id/flat/-946e-0453-d841-%402ndquadrant.com

Since PostgreSQL 12 we have generated columns. The problem was, though, that once marked as generated – we couldn't make it not generated.

Consider such a simple table:

=$ create table test (
    id serial primary key,
    a int4 not null default 0,
    b int4 not null generated always AS ( 3 * a ) stored
);
CREATE TABLE
 
=$ \d test
                             Table "public.test"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 a      | integer |           | not null | 0
 b      | integer |           | not null | generated always as (3 * a) stored
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
 
=$ insert into test (a) values (12);
INSERT 0 1
 
=$ select * from test;
 id | a  | b  
----+----+----
  1 | 12 | 36
(1 row)

If I'd want to drop default from id – no problem:

=$ alter table test alter column id drop default;
ALTER TABLE
 
=$ \d test
                             Table "public.test"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | 
 a      | integer |           | not null | 0
 b      | integer |           | not null | generated always as (3 * a) stored
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

But column b can't be changed:

=$ alter table test alter column b drop default;
ERROR:  column "b" of relation "test" is a generated column

In Pg13 the error is more informative:

=$ alter table test alter column b drop default;
ERROR:  column "b" of relation "test" is a generated column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.

And now, I can:

=$ alter table test alter column b drop expression;
ALTER TABLE
 
=$ \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 a      | integer |           | not null | 0
 b      | integer |           | not null | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Great. Now it's just simple column. What about values that were there previously?

=$ select * from test;
 id | a  | b  
----+----+----
  1 | 12 | 36
(1 row)

and new inserts:

=$ insert into test (a,b) values (2,0);
INSERT 0 1
 
=$ select * from test;
 id | a  | b  
----+----+----
  1 | 12 | 36
  3 |  2 |  0
(2 rows)

All sane, and working. Thanks a lot Peter 🙂

5 thoughts on “Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION”

  1. So does changing it to a regular column cause writes to be performed, or are writes only made with UPDATEs? Just wondering what happens if you have a huge table.

  2. @Thom:

    In a table with 10,000,000 rows, 422MB, drop expression took 3.6ms. So I guess all the writes happened while I was inserting data. Which is expected given that it was defined as GENERATED … STORED.

Comments are closed.