--- Den ons 2010-04-28 skrev Tom Lane <t...@sss.pgh.pa.us>:
> Fra: Tom Lane <t...@sss.pgh.pa.us>
> Emne: Re: [HACKERS] Add column if not exists (CINE)
> Til: "Robert Haas" <robertmh...@gmail.com>
> Kopi: "Andrew Dunstan" <and...@dunslane.net>, "Takahiro Itagaki"
> <itagaki.takah...@oss.ntt.co.jp>, "Kjell Rune Skaaraas" <kjell...@yahoo.no>,
> pgsql-hackers@postgresql.org
> Dato: Onsdag 28. april 2010 17.20
> Robert Haas <robertmh...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
