Hi, I'm interested in adding more ergonomics to DDL commands, in particular supporting IF EXISTS for ALTER TABLE … ALTER COLUMN, so that if a column doesn't exist the command is skipped.
IF EXISTS is already supported in various places (e.g. ALTER TABLE … ADD COLUMN IF NOT EXISTS, and ALTER TABLE … DROP COLUMN IF EXISTS), but it's not available for any of the ALTER COLUMN sub commands. The motivation is to make it easier to write idempotent migrations that can be incrementally authored, such that they can be re-executed multiple times without having to write an "up" and "down" migration. https://github.com/graphile/migrate#idempotency elaborates a bit more on the approach. The current approach I see is to write something like: DO $$ BEGIN IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'myschema' AND table_name = 'mytable' AND column_name = 'mycolume') THEN ALTER TABLE myschema.mytable RENAME mycolume TO mycolumn; END IF; END $$; I think ideally the IF EXISTS would be added to all of the ALTER COLUMN commands, however for the moment I have only added it to the { SET | DROP } NOT NULL command to demonstrate the approach and see if there's in-principle support for such a change. Questions: 1. I assume this is not part of the SQL specification, so this would introduce more deviation to PostgreSQL. Is that accurate? Is that problematic? 2. I believe I'm missing some code paths for table inheritance, is that correct? 3. I haven't updated the documentation—is it correct to do that in doc/src/sgml/ref/alter_table.sgml? 4. This is my first time attempting to contribute to PostgreSQL, have I missed anything? -- Cheers, Brad
v1-0001-Add-IF-EXISTS-support-to-ALTER-COLUMN-SET-DROP-NO.patch
Description: Binary data
