Re: Add column if not exists (CINE)

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.

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.

regards, tom lane