Duplicate Primary Key in PostgreSQL table

Question

I created a table named 'link' in PostgreSQL database. Then I created another table 'link_copy' with same structure as 'link'

CREATE TABLE link (
id select PRIMARY KEY,
url VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL);

CREATE TABLE link_copy (LIKE link);

I copied a row from 'link' to 'link_copy' that had 'id' = 2. After that I copied all rows from 'link' to 'link_copy'. I was surprised to see row with 'id' = 2 twice in 'link_copy'. If I had selected 'id' column to be primary key (which is UNIQUE NOT NULL by default), then why did it allowed the 'id' = 2 row to be inserted in 'link_copy' twice?

I am using postgresql 9.5 on Mac with pgadmin III.

screenshot for final link_copy status

CREATE TABLE link ( id select PRIMARY KEY, ... ); this not gives error ? — Oto Shavadze
– Oto Shavadze, Commented Sep 18, 2017 at 15:11
@OtoShavadze I have updated the code. All the code ran successfully on SQL Editor. I have also added the final screenshot. — Gaurav Bishnoi
– Gaurav Bishnoi, Commented Sep 18, 2017 at 15:59

rd_nielsen · Accepted Answer · 2017-09-18 15:13:07Z

4

When you use the CREATE TABLE command to create a new table with the same structure as another, it duplicates the columns, but not all of the constraints. Your link_copy table will not have a primary key defined after it is created this way.

answered Sep 18, 2017 at 15:13

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaurav Bishnoi Over a year ago

Thanks! I checked the postgresql documentation for same: It states "Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be created on the new table only if INCLUDING INDEXES is specified."

3limin4t0r · Accepted Answer · 2019-06-12 14:25:00Z

1

Create table like below. This will create all constraints.

CREATE TABLE link_copy (LIKE link  including constraints including indexes);

edited Jun 12, 2019 at 14:25

3limin4t0r

21.4k3 gold badges38 silver badges61 bronze badges

answered Jun 12, 2019 at 13:00

Subodh

111 bronze badge

Collectives™ on Stack Overflow

Duplicate Primary Key in PostgreSQL table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related