Create Unique Index If Not Exists in Postgresql

why would CREATE INDEX IF NOT EXISTS timeout, with postgres?

CREATE INDEX IF NOT EXISTS idx_trades_ticker_ts ON exchange.trades (ticker, ts);

One of the first things this instruction will do is trying to acquire a lock of mode ShareLock on exchange.trades.
According to the documentation:

SHARE

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
protects a table against concurrent data changes.

Acquired by CREATE INDEX (without CONCURRENTLY).

If other concurrent transactions have already grabbed a lock that conflicts with this one, and they keep it for more than statement_timeout, your CREATE INDEX will time out. Typically, transactions that write into the table will need that lock level.

That the index already exists does not change the necessity of taking the lock, otherwise the evaluation of the NOT EXISTS clause would be subject to race conditions.

See Lock monitoring for how to check for locks at runtime.

Insert into PostgreSQL table if a unique column combination doesn't exist, and if it does, update the existing record

You need a unique index on both columns. It can be a composite primary key or a composite unique constraint, example:

create table tablename(
    user_id int, 
    item_id int, 
    test text,
    primary key (user_id, item_id)
);

Then use the simple proper syntax:

insert into tablename (user_id, item_id, test) 
values(1, 1, '1') 
on conflict (user_id, item_id) 
do update set test = excluded.test

Creating unique index seems to fail, but is created anyway?

This is documented in the manual

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID

If you don't want that behaviour, don't use CONCURRENTLY

PostgreSQL if not exists always throws syntax error

No need for PL/pgSQL, just use the IF NOT EXISTS option in the CREATE TABLE part:

CREATE TABLE IF NOT EXISTS topgun.session (
  sid varchar NOT NULL COLLATE "default",
  sess json NOT NULL,
  expire timestamp(6) NOT NULL,
  CONSTRAINT "session_pkey" PRIMARY KEY ("sid")
);

CREATE INDEX IF NOT EXISTS "IDX_session_expire" ON topgun.session ("expire");     

Postgres unique constraint vs index

I had some doubts about this basic but important issue, so I decided to learn by example.

Let's create test table master with two columns, con_id with unique constraint and ind_id indexed by unique index.

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

In table description (\d in psql) you can tell unique constraint from unique index.

Uniqueness

Let's check uniqueness, just in case.

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

It works as expected!

Foreign keys

Now we'll define detail table with two foreign keys referencing to our two columns in master.

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Well, no errors. Let's make sure it works.

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

Both columns can be referenced in foreign keys.

Constraint using index

You can add table constraint using existing unique index.

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Now there is no difference between column constraints description.

Partial indexes

In table constraint declaration you cannot create partial indexes.
It comes directly from the definition of create table ....
In unique index declaration you can set WHERE clause to create partial index.
You can also create index on expression (not only on column) and define some other parameters (collation, sort order, NULLs placement).

You cannot add table constraint using partial index.

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

---

Related Topics