Re: Add column if not exists (CINE)

--- Den ons 2010-04-28 skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Fra: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Emne: Re: [HACKERS] Add column if not exists (CINE)
> Til: "Robert Haas" <robertmhaas(at)gmail(dot)com>
> Kopi: "Andrew Dunstan" <andrew(at)dunslane(dot)net>, "Takahiro Itagaki" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, "Kjell Rune Skaaraas" <kjella79(at)yahoo(dot)no>, pgsql-hackers(at)postgresql(dot)org
> Dato: Onsdag 28. april 2010 17.20
> Robert Haas <robertmhaas(at)gmail(dot)com>
> writes:
> > I don't believe you are fairly stating the consensus
> from previous
> > discussion and I believe that you are actually in the
> minority on this
> > one.  I agree that we probably don't need to
> support this for object
> > types for which CREATE OR REPLACE is available or can
> be made
> > available, but that isn't feasible for all object
> types - tables and
> > columns being the obvious examples.
>
> What's obvious about it?  In particular, I should
> think that ADD OR
> REPLACE COLUMN would usefully be defined as "ADD if no such
> column,
> else ALTER COLUMN as necessary to match this spec". 
> Dropping the
> ALTER part of that has no benefit except to lazy
> implementors; it
> certainly is not more useful to users if they can't be sure
> of the
> column properties after issuing the command.

To me this construct seems horribly ambigious. Imagine I did a ALTER TABLE foo ADD COLUMN bar INTEGER NOT NULL UNIQUE DEFAULT 10, then an ALTER TABLE foo ADD OR REPLACE COLUMN bar BIGINT. Would I get a BIGINT NOT NULL UNIQUE DEFAULT 10 or a plain BIGINT? Either way I think one group will be disappointed because it either trashes all your other setup *or* forces you to call DROP NOT NULL, DROP DEFAULT etc. when you don't want it.

There's a reason why there's no ALTER TABLE foo SET COLUMN bar [definition]" and instead many statements. Remember it has to deal with all these possible column constraints in ADD COLUMN:

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
CHECK ( expression ) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

What about something like CHECK? Would you assume it's a complete set of CHECKs and drop the rest? Or just keep creating new CHECKs every time it is run? Dupe checking?

CINE has none of these problems, either the column didn't exist before so there's nothing to worry about or the command does nothing. True, you may have a borked column but not if you follow a simple design pattern of never recasting a column type but rather add a new, migrate your data and update your queries. And for the exceptions to that rule, you can add a ALTER COLUMN SET DATA TYPE (or any of the other ALTERs) after the CINE in your scipt. If the CINE triggered all is the latest version, if not the detailed ALTERs will change any column that needs changing. Clean and simple.

Regards,
Kjell Rune