Re: Default Value Retention After Dropping Default

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.
>
> In the following snippet, I first add a column with a default value, then drop
> that default. However, when I query the table, the column still retains the
> dropped default for existing rows:
>
>   SET client_min_messages=debug1;
>
>   DROP TABLE IF EXISTS foo CASCADE;
>   CREATE TABLE foo (id SERIAL PRIMARY KEY);
>
>   INSERT INTO foo (id) SELECT generate_series(1, 10000);
>
>   ALTER TABLE foo ADD COLUMN bar varchar(255) NOT NULL DEFAULT 'default';
>   ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
>
>   SELECT * from foo order by id desc limit 5;
>   --   id   |   bar
>   -- -------+---------
>   --  10000 | default
>   --   9999 | default
>   --   9998 | default
>   --   9997 | default
>   --   9996 | default
>
> In this example, even after dropping the default value from the bar column, the
> rows that were previously inserted (prior to dropping the default) still show
> 'default' as their value in the bar column.
>
> It does not see that the table has been rewritten or rescanned, otherwise the
> debug1 messages would be triggered.
>
> 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.

Yours,
Laurenz Albe

--

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.