305

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?

Improve this question
1

4 Answers 4

Reset to default
561

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.)
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.
@jpmc26 «If you want to let PostgreSQL generate the index name» You mean the constraint name?
@tuxayo, a unique-constraint is implemented via an index in Postgres (not to be pedantic).
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/…
59

Yes, you can. But if you have non-unique entries in your table, it will fail. Here is the how to add a unique constraint to your table in PostgreSQL 9.x:

 CREATE UNIQUE INDEX constraint_name ON table_name (columns);
Improve this answer

5 Comments

Thanks Zeck - nice 2y later answer but still appreciate that people still take the time! Tom
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...
@Strinder, how is that not a good thing? fix the duplicated data first.
@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.
Edited the answer for clarity
23

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);
Improve this answer

Comments

6

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.

Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.