Add support for PostgreSQL ALTER TABLE ... ADD COLUMN ... REFERENCES #1780

Describe the use case

PostgreSQL supports a special syntax for adding a column with a simple foreign key in one operation with the REFERENCES key. This is helpful during migrations since it skips validating the entire table if the column is nullable without a non-null default.

Databases / Backends / Drivers targeted

PostgreSQL

Example Use

Currently the Alembic operation:

op.add_column("foo", sa.Column("bar_id", sa.Integer(), sa.ForeignKey("bar.id"), nullable=True))

emits the following SQL:

ALTER TABLE foo ADD COLUMN bar_id INTEGER;

ALTER TABLE foo ADD FOREIGN KEY(bar_id) REFERENCES bar (id);

which causes an scan of all rows in foo to validate, even though the column is clearly all NULL since we just created it.

The single command

ALTER TABLE foo ADD COLUMN bar_id INTEGER REFERENCES bar (id);

would mark the foreign key as immediately valid which means the command is a very fast.

This can be a significant improvement for large tables.

Additional context

See this StackExchange post about the optimize of ADD COLUMN ... REFERENCES https://dba.stackexchange.com/a/343821

Have a nice day!