Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?

Question

I have the following table:

 tickername | tickerbbname  | tickertype
------------+---------------+------------
 USDZAR     | USDZAR Curncy | C
 EURCZK     | EURCZK Curncy | C
 EURPLN     | EURPLN Curncy | C
 USDBRL     | USDBRL Curncy | C
 USDTRY     | USDTRY Curncy | C
 EURHUF     | EURHUF Curncy | C
 USDRUB     | USDRUB Curncy | C

I don't want there to ever be more than one column for any given tickername/tickerbbname pair. I've already created the table and have lots of data in it (which I have already ensured meets the unique criteria). As it gets larger, though, room for error creeps in.

Is there any way to add a UNIQUE constraint at this point?

Solution 1

psql's inline help:

\h ALTER TABLE

Also documented in the postgres docs (an excellent resource, plus easy to read, too).

ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);

Solution 2

Yes, you can. But if you have non-unique entries on your table, it will fail. Here is the how to add unique constraint on your table. If you're using PostgreSQL 9.x you can follow below instruction.

CREATE UNIQUE INDEX constraint_name ON table_name (columns);

Solution 3

If you had a table that already had a existing constraints based on lets say: name and lastname and you wanted to add one more unique constraint, you had to drop the entire constrain by:

ALTER TABLE your_table DROP CONSTRAINT constraint_name;

Make sure tha the new constraint you wanted to add is unique/ not null ( if its Microsoft Sql, it can contain only one null value) across all data on that table, and then you could re-create it.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Solution 4

Yes, you can add a UNIQUE constraint after the fact. However, if you have non-unique entries in your table Postgres will complain about it until you correct them.

View more solutions

Comments

[Amir Ali Akbari]

possible duplicate of How do I ALTER a PostgreSQL table and make a column unique?

[Thomas Browne]

thanks @hhaamu. Yep did try the docs but your above is much more concise.

[Thomas Browne]

Thanks Zeck - nice 2y later answer but still appreciate that people still take the time! Tom

[jpmc26]

If you want to let PostgreSQL generate the index name, use ALTER TABLE tablename ADD UNIQUE (columns);. (Note that the CONSTRAINT keyword must be omitted.)

[Leonard]

I needed an answer to this very question and started googling for the docs. Instead of the Postgres documentation, I ran into this topic at StackOverflow. So although it's a good think to reference the official docs, it's also very good to give the answer for future visits. Thank you for that.

[Strinder]

That's not correct. In latest Postgres this leads also to the message like "Key (uuid)=(3a533772-07ac-4e76-b577-27a3878e2222) is duplicated. Query failed" if you have a value that is not unique...

[Jasen]

@Strinder, how is that not a good thing? fix the duplicated data first.

[Jasen]

select <column> from <table> group by 1 having count(*) > 1; will give a report on duplicated values.

[Strinder]

@Jasen That's totally clear. Just wanted to emphasize that the answer "But if you have non-unique entries on your table. Here is the how to add unique constraint on your table." will not work. Non-unique entries must of course always be consolidated beforehand.

[Xavier Ho]

Edited the answer for clarity

[tuxayo]

@jpmc26 «If you want to let PostgreSQL generate the index name» You mean the constraint name?

[Chris W.]

@tuxayo, a unique-constraint is implemented via an index in Postgres (not to be pedantic).

[Rahul Jha]

You can use unique constraint or unique index. Both works but ideally you should not use unique index alone without constraint. To Create unique constraint use ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);. You can read more here : dotnet-concept.com/Articles/2020/7/5800890/…