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

sql postgresql unique-constraint
Baishampayan Ghose picture Baishampayan Ghose · Jan 22, 2009 · Viewed 130.7k times · Source

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. Can anybody help me with this?

TIA

Answer

Baishampayan Ghose picture Baishampayan Ghose · Jan 22, 2009

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

ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);

Thanks Fred.

Related questions

Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?

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 …

Read More
How to delete duplicate entries?

I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add. What is the fastest …

Read More
Allow null in unique column

I've created the following table: CREATE TABLE MMCompany ( CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL, Name VARCHAR (150) NOT NULL, PhoneNumber VARCHAR(20) NOT NULL UNIQUE, Email VARCHAR(75) UNIQUE, CompanyLogo BYTEA ); The email column is unique and it causes a "bug" in my …

Read More