PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15422
PG Version10.5
OSDocker (postgres:latest) on Ubuntu 14.04
Opened2018-10-08 04:17:51+00
Reported byDale B
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15422
Logged by:          Dale B
Email address:      (redacted)
PostgreSQL version: 10.5
Operating system:   Docker (postgres:latest) on Ubuntu 14.04
Description:        

# Bug description

Foreign key is still created for an `alter table ... add column if not
exists ... references ... ;` statement even if the column exists. I would
expect no schema change to occur if the column already exists, but instead a
new foreign key constraint is added every time the query is run.

# Reproduction steps

On a fresh postgres 10.5 install:
```
create table if not exists test (id int primary key);
create table if not exists test2 (test_id int);
\d+ test;
alter table test2 add column if not exists test_id int references
test(id);
\d+ test;
alter table test2 add column if not exists test_id int references
test(id);
\d+ test;
```

This gives me the following output:
```
CREATE TABLE
CREATE TABLE
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

psql:test.sql:4: NOTICE:  column "test_id" of relation "test2" already
exists, skipping
ALTER TABLE
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "test2" CONSTRAINT "test2_test_id_fkey" FOREIGN KEY (test_id)
REFERENCES test(id)

psql:test.sql:6: NOTICE:  column "test_id" of relation "test2" already
exists, skipping
ALTER TABLE
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "test2" CONSTRAINT "test2_test_id_fkey" FOREIGN KEY (test_id)
REFERENCES test(id)
    TABLE "test2" CONSTRAINT "test2_test_id_fkey1" FOREIGN KEY (test_id)
REFERENCES test(id)

```

Messages

DateAuthorSubject
2018-10-08 04:17:51+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15422: add column statement with if not exists creates a new foreign key even if the column already exists