Re: Default Value Retention After Dropping Default

On Mon, Feb 24, 2025 at 9:37 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/24/25 03:50, Laurenz Albe wrote:
> > On Mon, 2025-02-24 at 20:56 +1300, Marcelo Fernandes wrote:
> >> I am experiencing an interesting behavior in PostgreSQL and would like
> to seek
> >> some clarification.
>
>
> >>
> >> Can anyone explain how PostgreSQL "knows about" the default value that
> has just
> >> been dropped and what is happened under the scenes? I am keen on a deep
> >> understanding on how Postgres achieves this.
> >
> > The "missing value" is stored in pg_attribute.admissingval:
> >
> > SELECT attmissingval
> > FROM pg_attribute
> > WHERE attrelid = 'foo'::regclass
> > AND attname = 'bar';
> >
> > attmissingval
> > ═══════════════
> > {default}
> > (1 row)
> >
> > That value is used for all rows that don't yet physically have the
> column.
>
> That answers this part of the process:
>
> ALTER TABLE foo ADD COLUMN bar varchar(255) NOT NULL DEFAULT 'default';
>
> I believe the OP is asking about this:
>
> ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
>
> Because if after dropping the DEFAULT you do this:
>
> INSERT INTO foo (id) SELECT generate_series(1001, 1010);
>
> You get:
>
> ERROR: null value in column "bar" of relation "foo" violates not-null
> constraint
> DETAIL: Failing row contains (1001, null).
>
> The DEFAULT is no longer in use, but the values still exist in the
> previously entered rows:
>

The alter table command established a persistent substitute value for the
new column, for all existing rows, when it was executed. While the value
of the substitute is equal to the non-volatile default specified for the
column it is an independent thing. Subsequently dropping or changing the
default does not impact this substitute value. There is no way to impact
the substitute value via SQL that I know of.

David J.