public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Marcelo Fernandes <[email protected]>
Cc: [email protected]
Subject: Re: Default Value Retention After Dropping Default
Date: Mon, 24 Feb 2025 10:06:09 -0700
Message-ID: <CAKFQuwZBnCNGHo35BaQB_-B4qgG39s2dHts3tf9URd1MJFd=zQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAM2F1VNZNeruO2N+_-Fq3JJ7bto35kOzomoN5A2dmstAWLi5tQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>

On Mon, Feb 24, 2025 at 9:37 AM Adrian Klaver <[email protected]>
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.

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Default Value Retention After Dropping Default
  In-Reply-To: <CAKFQuwZBnCNGHo35BaQB_-B4qgG39s2dHts3tf9URd1MJFd=zQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox