CODEWITHSUNDEEP
postgresql
Mo1
Mo1

Reputation: 379

Specifying UNIQUE constraints in PostgreSQL

I would appreciate your help with the blocker I have. I created a new table with the statement CREATE TABLE copy_table AS SELECT * FROM original_table because I wanted the second table with similar columns as the original table.

However, the second table came without the UNIQUE constraint that was in the original table. So far, I can insert data into the mentors' table but anything that required UNIQUE constraint is not working.

I tried to add the UNIQUE constraints but it's throwing this error: syntax error at or near "UNIQUE"

Unedited code 

CREATE TABLE IF NOT EXISTS mentors AS SELECT users.user_id AS mentor_id, 
first_name, last_name, email, address, password, bio, occupation, expertise, is_mentor, is_admin FROM users;

Edited code(Adding UNIQUE constraint) 

CREATE TABLE IF NOT EXISTS mentors AS SELECT users.user_id AS mentor_id(UNIQUE),first_name, last_name, email, address, password, bio, occupation, expertise, is_mentor, is_admin FROM users;

What am I doing wrong? Thanks.

Upvotes: 1

Views: 46

Answers (1)

klin
klin

Reputation: 121604

CREATE TABLE AS does not allow to define any constraints on the new table.

Use ALTER TABLE just after the table is created, e.g.:

ALTER TABLE mentors ADD UNIQUE(mentor_id)

Upvotes: 2

Related Questions