Per the docs, All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes. If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. The docs go on to say, [index_predicate are u]sed to allow inference of partial unique indexes … Read more
ON CONFLICT requires a unique index* to do the conflict detection. So you just need to create a unique index on both columns: t=# create table t (id integer, a text, b text); CREATE TABLE t=# create unique index idx_t_id_a on t (id, a); CREATE INDEX t=# insert into t values (1, ‘a’, ‘foo’); INSERT … Read more
Postgres hasn’t implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICT docs (emphasis mine): It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. Though it doesn’t give you shorthand for replacement, ON CONFLICT DO UPDATE … Read more
You can prevent future connections: REVOKE CONNECT ON DATABASE thedb FROM public; (and possibly other users/roles; see \l+ in psql) You can then terminate all connections to this db except your own: SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid(); On older versions pid was called procpid so you’ll have … Read more