Обсуждение: 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