BUG #15422: add column statement with if not exists creates a new foreign key even if the column already exists

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dabrown+pg(at)goscoutgo(dot)com
Subject: BUG #15422: add column statement with if not exists creates a new foreign key even if the column already exists
Date: 2018-10-08 04:17:51
Message-ID: 15422-1f1ee97a6a8c57df@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15422
Logged by: Dale B
Email address: dabrown+pg(at)goscoutgo(dot)com
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)

```

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-10-08 06:35:33 BUG #15423: A typo in the pg_basebackup
Previous Message Wim Tuitman 2018-10-07 06:25:09 No error or completion message dureing csv uload in pgadmin