On Fri, Nov 1, 2024 at 5:06 PM Marcelo Fernandes <marcefe...@gmail.com> wrote: > > Hi folks, > > We have this Tip box under the "Adding a Column" header here: > > - > https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN > > That says: > > > From PostgreSQL 11, adding a column with a constant default value no longer > > means that each row of the table needs to be updated when the ALTER TABLE > > statement is executed. Instead, the default value will be returned the next > > time the row is accessed, and applied when the table is rewritten, making > > the > > ALTER TABLE very fast even on large tables. > > I'm just seeking clarification if this advice is true **even for** new columns > declared with NOT NULL? > you can use event_trigger to test it.
https://www.postgresql.org/docs/current/event-trigger-definition.html The table_rewrite event occurs just before a table is rewritten by some actions of the commands ALTER TABLE and ALTER TYPE. While other control statements are available to rewrite a table, like CLUSTER and VACUUM, the table_rewrite event is not triggered by them. --- following tests copy from https://github.com/postgres/postgres/blob/d893a299ce68f56522073a1b43d65764a552ae0d/src/test/regress/sql/fast_default.sql#L47 CREATE OR REPLACE FUNCTION log_rewrite() RETURNS event_trigger LANGUAGE plpgsql as $func$ declare this_schema text; begin RAISE NOTICE 'rewriting table % for reason %', pg_event_trigger_table_rewrite_oid()::regclass, pg_event_trigger_table_rewrite_reason(); end; $func$; CREATE EVENT TRIGGER has_rewrite ON table_rewrite EXECUTE PROCEDURE log_rewrite(); create table t1(a int); insert into t1 select 1; alter table t1 add column b double precision not null default 11; alter table t1 add column c double precision not null default random(); alter table t1 add column d timestamptz not null default now(); alter table t1 add column e timestamptz not null default current_timestamp; alter table t1 ADD column f int DEFAULT (random() * 10000)::int; alter table t1 ADD column g int not null DEFAULT (random() * 10000)::int;
