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)
```
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 |