How to Alter a Postgresql Table and Make a Column Unique

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.

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

In PostgreSQL, add a column with unique values according to specific column ordering / sorting

One approach is to add the id column, set the value and then convert it to an identity. You have to be careful, because you want the numbering to start at 15 (for your sample data):

alter table db1 add column id int not null default 0;
update db1
    set id = d.new_id
    from (select db1.*, db1.ctid, row_number() over (order by member, service_date) as new_id
          from db1
         ) d
    where db1.ctid = d.ctid;

alter table db1 alter column id drop default;

alter table db1 alter column id add generated always as identity  (start with 15) ;

alter table db1 add primary key (id);

Here is a db<>fiddle.

Postgresql alter table column type to unique not null

You cannot create 2 constraints with one single statement. And you have to use PostgreSQL syntax.

alter table users alter column email set not null;
alter table users add constraint email_unique unique (email);

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;

Is it possible to add a column with unique constraint in one go?

You need a second ADD option:

ALTER TABLE corporates
  ADD COLUMN email varchar(100) NOT NULL,
  ADD CONSTRAINT corporates_email_key UNIQUE (email);

In Postgresql, force unique on combination of two columns

CREATE TABLE someTable (
    id serial PRIMARY KEY,
    col1 int NOT NULL,
    col2 int NOT NULL,
    UNIQUE (col1, col2)
)

autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10).

If col1 and col2 make a unique and can't be null then they make a good primary key:

CREATE TABLE someTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY (col1, col2)
)

Alter Unique Constraint in PSQL

[How can I] Locate this constraint with psql

We can get the same error message if we violate a primary key constraint.

# alter table addresses add constraint addresses_pk primary key (id);
ALTER TABLE
# insert into addresses values (1, 2, 4, 4);
ERROR:  duplicate key value violates unique constraint "addresses_pk"
DETAIL:  Key (id)=(1) already exists.
# 

Try searching the information schema where constraint_type = 'PRIMARY KEY'.

Note that we don't have to give a primary key constraint a name, as Postgres will generate a default of <table_name>_pkey. So for this to be the solution in your case it means whoever created the primary key gave it an explicit name of address_uniq, which would be confusing.

So a more likely possibility is that you have a unique index on those columns. Indexes don't show up in the information schema. You can check like this:

select * from pg_indexes where tablename = 'addresses';

[How can I] Alter/Update this constraint, and add 1 more column to it

If your problem is an index then do this:

# drop index address_uniq;
DROP INDEX
# create unique index address_uniq on addresses (address, hostname, coin_id);
CREATE INDEX
# 

If it turns out it is a primary key constraint it's a similar process:

# alter table addresses drop constraint address_uniq;
ALTER TABLE
# alter table addresses add constraint address_uniq primary key (address, hostname,coin_id);
ALTER TABLE
# 

Alter column to make it a derived column

You can try to use Generated Columns

If this column exists in your table, we might need to drop it before creating it.

Due to the document might not support ALTER COLUMN with GENERATED

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

ALTER TABLE Payment DROP COLUMN Taxes;

ALTER TABLE Payment
ADD Taxes  [type of Taxes]  GENERATED ALWAYS AS (0.12 * PaySlip) stored;

sqlfiddle

---

Related Topics