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.
I figured it out from the PostgreSQL docs, the exact syntax is:
ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn); Thanks Fred.
ALTER TABLE the_table DROP CONSTRAINT IF EXISTS constraint_name;Or, have the DB automatically assign a constraint name using:
ALTER TABLE foo ADD UNIQUE (thecolumn); 
mytable_mycolumn_key I'm quite happy with that :-)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); 
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);Try the following
ALTER TABLE table_name ADD UNIQUE (column_name); 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.
create unique index on foo_table (permalink)