Re: Re: BUG #15361: Add column if not exists create duplicate constraint

On 2018-09-02 12:38 PM, Tom Lane wrote:
> The problem is precisely that it's very fuzzy exactly what THIS is.
> The case at hand, with an FK constraint, is maybe even a better
> demonstration of that than the cases we considered previously.
> Currently, if the column exists but lacks an FK constraint, the code
> will make sure one gets added; with what you propose, it won't.
> You can argue all day about which semantics are more useful, but I don't
> see that there's a bright line dividing right from wrong here ---
> especially since, AFAICS, there's not currently any way for a user to
> get the other behavior if she doesn't like the one we choose.
I would like to suggest that to find the bright line, we look at the "IF
NOT EXISTS" of the CREATE TABLE command:

|IF NOT EXISTS|

Do not throw an error if a relation with the same name already
exists. A notice is issued in this case. Note that there is no
guarantee that the existing relation is anything like the one
that would have been created.

So, that version doesn't automatically fix the table to match the new
specification.

Also, the IF NOT EXISTS of CREATE SEQUENCE:

|IF NOT EXISTS|

Do not throw an error if a relation with the same name already
exists. A notice is issued in this case. Note that there is no
guarantee that the existing relation is anything like the
sequence that would have been created - it might not even be a
sequence.

This command doesn't even guarantee the result is a sequence!

My (admittedly, unimportant) opinion is that this is a bug.  For CREATE
TABLE and CREATE SEQUENCE, there are two possible states that the
database can be in.  Either with the old table/sequence definition, or
with the new definition.

With the current behaviour of ADD COLUMN IF NOT EXISTS with
CONSTRAINT(s), you cannot know what the resulting state is, other than
that there will be one more constraint added to the column.

Furthermore, I submit the following interaction:

jstrachan=# create table test (foo integer);
CREATE TABLE
jstrachan=# alter table test add column if not exists foo boolean;
ALTER TABLE
jstrachan=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 foo    | integer |

If the ADD COLUMN command should automatically fix the constraints of
the possibly-added column, then it should also automatically change the
datatype!

Thanks very much for all your work, and PostgreSQL is awesome.

Jamie Strachan