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.