Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN

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.

6 thoughts on “Waiting for 9.6 – Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN”

  1. What happens if the types are different?

    It would be nice if we get an error if they differ.

  2. What about add after and before a column! It’s feature is still missing!

  3. @Alessandro ,

    What’s the reason or needing that? I’m having hard time figuring out why column “order” should matter.

  4. 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?

  5. @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.

Comments are closed.