BUG #14346: CREATE TABLE xxx (LIKE yyy INCLUDING ALL) fails
The following bug has been logged on the website:
Bug reference: 14346
Logged by: Massimo Roscio
Email address: mroscio@tin.it
PostgreSQL version: 9.5.4
Operating system: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled
Description:
xxx is created with all relevant columns
When yyy has no primary keys, all constraints are duly created on xxx.
When yyy has a primary key named pk_yyy, a primary key named xxx_pkey is
created, all other constraints are lost.
Please feel free to ask for more information if needed.
regards
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
mroscio@tin.it writes:
When yyy has no primary keys, all constraints are duly created on xxx.
When yyy has a primary key named pk_yyy, a primary key named xxx_pkey is
created, all other constraints are lost.
Works for me ...
regression=# create table yyy (f1 int constraint pk_yyy primary key, f2 int unique);
CREATE TABLE
regression=# \d yyy
Table "public.yyy"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
Indexes:
"pk_yyy" PRIMARY KEY, btree (f1)
"yyy_f2_key" UNIQUE CONSTRAINT, btree (f2)
regression=# create table xxx (like yyy including all);
CREATE TABLE
regression=# \d xxx
Table "public.xxx"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
Indexes:
"xxx_pkey" PRIMARY KEY, btree (f1)
"xxx_f2_key" UNIQUE CONSTRAINT, btree (f2)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thank you for your reply.
I understand that getting the primary key as xxx_pkey , while I
expected pk_xxx, is not a bug, it's a feature.
However I insist about foreign keys: source table is present with 13
rows in information_schema.key_column_usage,
while destination table only has one.
To provide a complete example, I must "sanitize" the names which are
linked to the product I am working on. It will take some time.
There are ten single-column foreign keys, one three-column foreign key.
Primary key is single-column, numeric, named "oid".
Kind regards
Massimo Roscio
Show quoted text
On 29/09/16 14:59, Tom Lane wrote:
mroscio@tin.it writes:
When yyy has no primary keys, all constraints are duly created on xxx.
When yyy has a primary key named pk_yyy, a primary key named xxx_pkey is
created, all other constraints are lost.Works for me ...
regression=# create table yyy (f1 int constraint pk_yyy primary key, f2 int unique);
CREATE TABLE
regression=# \d yyy
Table "public.yyy"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
Indexes:
"pk_yyy" PRIMARY KEY, btree (f1)
"yyy_f2_key" UNIQUE CONSTRAINT, btree (f2)regression=# create table xxx (like yyy including all);
CREATE TABLE
regression=# \d xxx
Table "public.xxx"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
Indexes:
"xxx_pkey" PRIMARY KEY, btree (f1)
"xxx_f2_key" UNIQUE CONSTRAINT, btree (f2)regards, tom lane
"M. Roscio" <mroscio@tin.it> writes:
However I insist about foreign keys: source table is present with 13
rows in information_schema.key_column_usage,
while destination table only has one.
LIKE is not documented as copying foreign key constraints, regardless
of whether there's a primary key or not. INCLUDING CONSTRAINTS
is specifically stated to control CHECK constraints only.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs