Specifying UNIQUE constraints in PostgreSQL

Question

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.

klin · Accepted Answer · 2019-08-28 21:15:32Z

2

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)

answered Aug 28, 2019 at 21:15

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Specifying UNIQUE constraints in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related