How to Add a Unique Constraint to a Postgresql Table, After It's Already Created

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

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);

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

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

ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);

Thanks Fred.

Altering Postgres table to have unique with constraint when duplicates records alredy exist

I fail to see the purpose of the locks or of the two delete statements, but to execute the triggers right away, run this before the ALTER TABLE:

SET CONSTRAINTS ALL IMMEDIATE;

Make a previously existing foreign key column have a unique constraint in postgres

After digging some more found quite a simple way to do this, was clearly originally off target.

To add a unique constraint to a column:

ALTER TABLE "myTable"
ADD CONSTRAINT "myUniqueKeyNameOfChoice" UNIQUE ("myColumn");

To remove it:

ALTER TABLE "myTable"
DROP CONSTRAINT "myUniqueKeyNameOfChoice";

Unexpected creation of duplicate unique constraints in Postgres

You can create multiple unique constraints on the same column as long as they have different names, simply because there is nothing in the PostgreSQL code that forbids that. Each unique constraint will create a unique index with the same name, because that is how unique constraints are implemented.

This can be a valid use case: for example, if the index is bloated, you could create a new constraint and then drop the old one.

But normally, it is useless and does harm, because each index will make data modifications on the table slower.

Postgres: Add constraint if it doesn't already exist

This might help, although it may be a bit of a dirty hack:

create or replace function create_constraint_if_not_exists (
    t_name text, c_name text, constraint_sql text
) 
returns void AS
$$
begin
    -- Look for our constraint
    if not exists (select constraint_name 
                   from information_schema.constraint_column_usage 
                   where table_name = t_name  and constraint_name = c_name) then
        execute constraint_sql;
    end if;
end;
$$ language 'plpgsql'

Then call with:

SELECT create_constraint_if_not_exists(
        'foo',
        'bar',
        'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')

Updated:

As per Webmut's answer below suggesting:

ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;

That's probably fine in your development database, or where you know you can shut out the apps that depend on this database for a maintenance window.

But if this is a lively mission critical 24x7 production environment you don't really want to be dropping constraints willy nilly like this. Even for a few milliseconds there's a short window where you're no longer enforcing your constraint which may allow errant values to slip through. That may have unintended consequences leading to considerable business costs at some point down the road.

---

Related Topics