On 30th of July, Andrew Dunstan committed patch:
Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and Michael Paquier.
Nice. I always like when there is new “IF EXISTS" or “IF NOT EXISTS", because it makes my life as dba simpler.
This time, we got the addition for adding columns. Up to 9.5, if you wanted to make a sql script that adds column – it would fail if the column already existed:
$ ALTER TABLE test ADD COLUMN z text; ALTER TABLE $ ALTER TABLE test ADD COLUMN z text; ERROR: COLUMN "z" OF relation "test" already EXISTS
This error could be handled by savepoint, or DO command, with error catching, but it's not really nice. Now, however, we can:
$ ALTER TABLE test ADD COLUMN IF NOT EXISTS z text; ALTER TABLE $ ALTER TABLE test ADD COLUMN IF NOT EXISTS z text; NOTICE: COLUMN "z" OF relation "test" already EXISTS, skipping ALTER TABLE
Please note that on the 2nd run I didn't get ERROR, just NOTICE – and the result was a success. Simple, but definitely welcome, thanks.
What happens if the types are different?
It would be nice if we get an error if they differ.
What about add after and before a column! It’s feature is still missing!
Show!
@Alessandro ,
What’s the reason or needing that? I’m having hard time figuring out why column “order” should matter.
Of course column order does not matter… if you are a machine. But humans are different! Why Oracle, SQL Server, DB2, MySQL etc etc etc have this “useless” feature?
@Alessandro:
you are welcome to try to lobby for it (on pgsql-hackers mailing list, I think), or, even better, write it and provide as patch.
I, for one, don’t care for it.
Databases are much more often read by machines than by people, and even with people – I can easily do select field, field, field in any order I need.