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

Question

I have a table in PostgreSQL where the schema looks like this:

CREATE TABLE "foo_table" (
    "id" serial NOT NULL PRIMARY KEY,
    "permalink" varchar(200) NOT NULL,
    "text" varchar(512) NOT NULL,
    "timestamp" timestamp with time zone NOT NULL
)

Now I want to make the permalink unique across the table by ALTER-ing the table.

`create unique index on foo_table (permalink)` – Oct 20 '14 at 17:48 — , Oct 20 '14 at 17:48

score 413 · Accepted Answer · edited May 23 '17 at 12:02

413

I figured it out from the PostgreSQL docs, the exact syntax is:

ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);

Thanks Fred.

edited May 23 '17 at 12:02

Community

1
1

answered Jan 22 '09 at 15:22

Baishampayan Ghose

19,928
10
56
60

7

In case you need to reverse this. `ALTER TABLE the_table DROP CONSTRAINT IF EXISTS constraint_name;` – vkozyrev Nov 05 '22 at 06:20

score 352 · Answer 2 · edited Oct 20 '14 at 17:12

352

Or, have the DB automatically assign a constraint name using:

ALTER TABLE foo ADD UNIQUE (thecolumn);

edited Oct 20 '14 at 17:12

rdrey

9,379
4
40
52

answered Sep 04 '11 at 03:26

Clint Pachl

10,848
6
41
42

2

If you do this, postgres is going to create the constraint but is the name is going to "automatically" assign is the word "add". I have just tried it – Santi Jan 16 '20 at 23:44
7

When I use the syntax above, Postgress creates the new contraint with the name `mytable_mycolumn_key` I'm quite happy with that :-) – Adam Jul 06 '20 at 11:11

score 63 · Answer 3 · edited Apr 14 '19 at 17:36

63

it's also possible to create a unique constraint of more than 1 column:

ALTER TABLE the_table 
    ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

edited Apr 14 '19 at 17:36

isapir

21,295
13
115
116

answered Nov 16 '16 at 09:55

Stefan

631
5
2

This doesn't seem to work for something like: `ALTER TABLE actions ADD CONSTRAINT actions_unique_constraint UNIQUE (payload::text, name);` or `ALTER TABLE actions ADD CONSTRAINT actions_unique_constraint UNIQUE ((payload::text), name);` – writofmandamus Oct 06 '17 at 22:22

score 5 · Answer 4 · answered Feb 09 '23 at 13:45

5

Try the following

ALTER TABLE table_name ADD UNIQUE (column_name);

answered Feb 09 '23 at 13:45

MatheusRitchielle

51
1
1

score 1 · Answer 5 · edited May 11 '23 at 10:47

To make a column unique in a PostgreSQL table, you can use the ALTER TABLE statement with the ADD CONSTRAINT clause.

Here's an example SQL statement

ALTER TABLE mytable
ADD CONSTRAINT unique_column_name UNIQUE (column_name);

In above statement, mytable is the name of the table you want to modify, column_name is the name of the column you want to make unique, and unique_column_name is a name you choose for the unique constraint.

If there are already duplicate values in the column you want to make unique, the ALTER TABLE statement will fail. You will need to remove or modify the duplicates before you can add the unique constraint.

Also, keep in mind that making a column unique will create an index on that column, which can impact performance if the table is very large or the column is frequently updated.

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

5 Answers5

Linked

Related