Thread: Postgres 12 - default value for text column

On Wed, 5 Aug 2020 at 08:36, Raj Gandhi <raj01gandhi@gmail.com> wrote:
> The following alter table with default set to very large text used to work in Postgres 10 but fails in Postgres 12
withERROR:  row is too big: size 12960, maximum size 8160
 

I didn't go to the trouble of debugging this, but I imagine this is
due to "Allow ALTER TABLE to add a column with a non-null default
without doing a table rewrite" mentioned in
https://www.postgresql.org/docs/11/release-11.html

In PG10 the table would have been rewritten when you add a NOT NULL
column with a DEFAULT. From PG11 onwards no rewrite takes place and
the default value is stored in pg_attribute.  Since pg_attribute does
not have a TOAST table, it's not possible to add NOT NULL columns
which have default values that won't fit in a heap page.

> The following two variants works in Postgres 12 without any error:
>
> create table test (id int);
> alter table test1 add column license text
> alter table test1 alter column license SET DEFAULT   '<insert default text with size more than 8160 >'

This behaves differently since existing rows won't receive the DEFAULT
value. Only new rows will. PostgreSQL12  does not need to store the
missing value in pg_attribute when you do this. Existing rows will
just have a NULL value for the new column.

> create table test (id int, license text  DEFAULT   '<insert default text with size more than 8160 >' );

Likewise. No missing value needs to be stored here as no rows exist
that need that value. Rows that are added with no value specified for
the license column will just have the DEFAULT value, which is the one
stored in pg_attrdef.

David