Re: Add column if not exists (CINE)

On Wed, Apr 28, 2010 at 2:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Robert Haas wrote:
>>> Well, how would you define CREATE OR REPLACE TABLE?
>
>> It the table doesn't exist, create it. If it exists with the same name
>> and same columns and constraints and all, do nothing. Otherwise throw an
>> error.
>
>> Maybe it should also check that the existing table is empty.
>
> The last bit doesn't seem to make sense.  If you want an empty table,
> you can do DROP IF EXISTS and then CREATE.  ISTM that the use-cases
> where you don't want to do that are cases where you don't want to lose
> existing data.

Right.

> For either CINE or COR, there are a number of issues that are being
> hand-waved away here: is it OK to change ownership and/or permissions?
> What about foreign key constraints relating this table to others?
> For that matter it's not real clear that indexes, check constraints,
> etc should be allowed to survive.  If they are allowed to survive then
> CINE TABLE is just the tip of the iceberg: to do anything useful you'd
> also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc.
> And the more of those you add, the more problematic it gets to allow
> existing objects that don't quite match what the command says.
>
> Any of these commands are headache-y for something as complicated
> as a table.  I'm not at all impressed by the argument that mysql
> does it, because they are *notorious* for being willing to ship
> half-baked solutions.

We can artificially make this problem as complicated as we wish, but
the people who are asking for this feature (including me) will, I
believe, be quite happy with a solution that throws, say, a NOTICE
instead of an ERROR when the object already exists, and then returns
without doing anything further. There are very few, if any,
definitional issues here, except by people who are brainstorming crazy
alternative behaviors whose actual usefulness I very much doubt.

CREATE OR REPLACE is indeed much more complicated. In fact, for
tables, I maintain that you'll need to link with -ldwim to make it
work properly.

...Robert