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
