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.

  1. 6 comments

  2. # Daniel Cristian
    Aug 19, 2015

    What happens if the types are different?

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

  3. # Alessandro
    Aug 20, 2015

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

  4. # Vinicius
    Aug 20, 2015

    Show!

  5. Aug 24, 2015

    @Alessandro ,

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

  6. # Alessandro
    Aug 26, 2015

    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?

  7. Aug 26, 2015

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

Leave a comment