Duplicate Primary Key in PostgreSQL table

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

Answer

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.

Advertisement

Answer