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.
