268

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?

Chris W.
  • 1,680
  • 16
  • 35
Thomas Browne
  • 23,824
  • 32
  • 78
  • 121
  • 2
    possible duplicate of [How do I ALTER a PostgreSQL table and make a column unique?](http://stackoverflow.com/questions/469471/how-do-i-alter-a-postgresql-table-and-make-a-column-unique) – Amir Ali Akbari Jul 04 '15 at 10:23

4 Answers4

503

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);
Marc Bannout
  • 388
  • 4
  • 15
hhaamu
  • 6,851
  • 3
  • 19
  • 13
  • 6
    thanks @hhaamu. Yep did try the docs but your above is much more concise. – Thomas Browne Jul 28 '09 at 20:17
  • 188
    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.) – jpmc26 Nov 26 '14 at 01:41
  • 6
    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. – Leonard Nov 26 '14 at 07:42
  • @jpmc26 «If you want to let PostgreSQL generate the index name» You mean the constraint name? – tuxayo Sep 06 '17 at 08:43
  • 4
    @tuxayo, a unique-constraint is implemented via an index in Postgres (not to be pedantic). – Chris W. Jan 11 '18 at 20:36
  • 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 : http://dotnet-concept.com/Articles/2020/7/5800890/Common-queries-in-PostgreSQL-Database-Create-Alter-Drop – Rahul Jha Jul 23 '20 at 12:20
51

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);
Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
Zeck
  • 6,433
  • 21
  • 71
  • 111
  • 3
    Thanks Zeck - nice 2y later answer but still appreciate that people still take the time! Tom – Thomas Browne Sep 07 '11 at 20:54
  • 1
    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 Dec 03 '15 at 07:39
  • 4
    @Strinder, how is that not a good thing? fix the duplicated data first. – Jasen Aug 26 '16 at 22:55
  • 4
    @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. – Strinder Aug 29 '16 at 06:41
  • Edited the answer for clarity – Xavier Ho Aug 02 '17 at 10:21
19

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);
Lucas Campos
  • 191
  • 1
  • 3
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.

Jordan S. Jones
  • 13,703
  • 5
  • 44
  • 49
  • 9
    `select from group by 1 having count(*) > 1;` will give a report on duplicated values.
     – Jasen Aug 26 '16 at 22:57