Thread: BUG #14346: CREATE TABLE xxx (LIKE yyy INCLUDING ALL) fails

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM0NgpMb2dnZWQgYnk6ICAg
ICAgICAgIE1hc3NpbW8gUm9zY2lvCkVtYWlsIGFkZHJlc3M6ICAgICAgbXJv
c2Npb0B0aW4uaXQKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuNApPcGVyYXRp
bmcgc3lzdGVtOiAgIFBvc3RncmVTUUwgOS41LjQgb24geDg2XzY0LXBjLWxp
bnV4LWdudSwgY29tcGlsZWQKRGVzY3JpcHRpb246ICAgICAgICAKCnh4eCBp
cyBjcmVhdGVkIHdpdGggYWxsIHJlbGV2YW50IGNvbHVtbnMNCg0KV2hlbiB5
eXkgaGFzIG5vIHByaW1hcnkga2V5cywgYWxsIGNvbnN0cmFpbnRzIGFyZSBk
dWx5IGNyZWF0ZWQgb24geHh4Lg0KDQpXaGVuIHl5eSBoYXMgYSBwcmltYXJ5
IGtleSBuYW1lZCBwa195eXksIGEgcHJpbWFyeSBrZXkgbmFtZWQgeHh4X3Br
ZXkgaXMKY3JlYXRlZCwgYWxsIG90aGVyIGNvbnN0cmFpbnRzIGFyZSBsb3N0
Lg0KDQpQbGVhc2UgZmVlbCBmcmVlIHRvIGFzayBmb3IgbW9yZSBpbmZvcm1h
dGlvbiBpZiBuZWVkZWQuDQoNCnJlZ2FyZHMNCgoK

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

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


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