Sample Header Ad - 728x90

Do you need NOT VALID when adding new foreign key column

2 votes
2 answers
560 views
I know that adding a foreign key constraint requires a table scan and a SHARE ROW EXCLUSIVE lock on both tables. To prevent the possibly lengthy table scan the constraint can be added with the NOT VALID approach. But I'm wondering, when adding a new column, should you also use NOT VALID or is Postgres smart enough to recognize it's a new column and thus the whole table doesn't need to be scanned? I'm using Django, and the generated SQL of adding a foreign key column looks like this:
ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;
Asked by Ruud van den Boomen (123 rep)
Nov 22, 2024, 12:35 PM
Last activity: Nov 24, 2024, 09:13 PM