Coder Perfect

Is it possible to apply a UNIQUE constraint to an existing PostgreSQL table?


The table below is what I have:

 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 any given tickername/tickerbbname pair to have more than one column. I’ve already constructed the table and filled it with information (which I have already ensured meets the unique criteria). However, as it grows larger, there is more space for error.

Is it possible to include a UNIQUE constraint at this point?

Asked by Thomas Browne

Solution #1

psql’s inline help:


The postgres docs also include information on this (an excellent resource, plus easy to read, too).

ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);

Answered by hhaamu

Solution #2

Yes, you certainly can. However, if your table contains non-unique entries, it will fail. Here’s how to make a table with a unique constraint. You can use the instructions below if you’re running PostgreSQL 9.x.

CREATE UNIQUE INDEX constraint_name ON table_name (columns);

Answered by Zeck

Solution #3

If you wished to add another unique constraint to a table with existing constraints based on, say, name and lastname, you had to drop the entire constraint by:

ALTER TABLE your_table DROP CONSTRAINT constraint_name;

Make sure the new constraint you wish to add is unique/not null across all data in that table (if it’s Microsoft SQL, it can only include one null value), and then re-create it.

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

Answered by Lucas Campos

Solution #4

Yes, a UNIQUE constraint can be added after the fact. However, if your table contains non-unique entries, Postgres will complain about it until you fix it.

Answered by Jordan S. Jones

Post is based on