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 EXISTSDo 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 EXISTSDo 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