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:
SELECT * from foo order by id desc limit 5;
id | bar
-------+---------
10000 | default
9999 | default
9998 | default
9997 | default
9996 | default
Yours,
Laurenz Albe
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
