Re: Postgres 12 - default value for text column

Great, thanks David for the explanation.

On Tue, Aug 4, 2020 at 4:59 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 5 Aug 2020 at 08:36, Raj Gandhi <raj01gandhi(at)gmail(dot)com> wrote:
> > The following alter table with default set to very large text used to
> work in Postgres 10 but fails in Postgres 12 with ERROR: 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
>