How to add constraint if not exists

Adding constraint using

alter table dok add constraint dok_during_exclude EXCLUDE USING gist (pais2obj WITH =, during WITH &&)

throws error

ERROR: relation "dok_during_exclude" already exists

Queries

select *   from information_schema.table_constraints    where    
constraint_name ilike 'dok_during_exclude'

and

select *  from information_schema.tables where table_name ilike 'dok_during_exclude'

Return no data. How to check if constraint exists ?

Using

 alter table dok add constraint if not exists dok_during_exclude EXCLUDE USING gist (pais2obj WITH =, during WITH &&)

Throws error

syntax error at or near "not"

Tried in Postgres 17 and in Postgres 12.

Solution

It might not necessarily be a table or constraint but all relations can be found in pg_class.relname.
_{demo at db<>fiddle}

create table dok(
   id int primary key
  ,pais2obj text
  ,during tstzrange);
create view dok_during_exclude
  as table dok;

alter table dok 
  add constraint dok_during_exclude EXCLUDE 
  USING gist ( pais2obj WITH =
              ,during WITH &&);

ERROR:  relation "dok_during_exclude" already exists

select relnamespace::regnamespace as schema_name
      ,case relkind
       when 'r' then 'ordinary table'
       when 'i' then 'index'
       when 'S' then 'sequence'
       when 't' then 'TOAST table'
       when 'v' then 'view'
       when 'm' then 'materialized view'
       when 'c' then 'composite type'
       when 'f' then 'foreign table'
       when 'p' then 'partitioned table'
       when 'I' then 'partitioned index'
       end as relkind_,*
from pg_class
where relname='dok_during_exclude';

schema_name	relkind_	oid	relname	relnamespace	reltype	reloftype	relowner	relam	relfilenode	reltablespace	relpages	reltuples	relallvisible	reltoastrelid	relhasindex	relisshared	relpersistence	relkind	relnatts	relchecks	relhasrules	relhastriggers	relhassubclass	relrowsecurity	relforcerowsecurity	relispopulated	relreplident	relispartition	relrewrite	relfrozenxid	relminmxid	relacl	reloptions	relpartbound
public	view	8580778	dok_during_exclude	2200	8580780	0	8580768	0	0	0	0	-1	0	0	f	f	p	v	3	0	t	f	f	f	f	t	n	f	0	0	0	null	null	null

You can rename the old thing and add the constraint making sure it's not on the table yet by trying to remove it.

There's no if not exists clause for alter table..add constraint unfortunately but there is alter table..drop constraint if exists, so you can drop and re-add it if the table isn't too big.

alter view dok_during_exclude
  rename to v_dok;

alter table dok 
   drop constraint if exists dok_during_exclude
  ,add constraint dok_during_exclude EXCLUDE 
   USING gist ( pais2obj WITH =
               ,during WITH &&);

There's unfortunately no static method of dropping or renaming the relation that's in the way without knowing its type, and while if not exists can handle collisions with things of a different type, the constraint seems to be an exception.

You can use dynamic plpgsql to perform the drop/rename dynamically, constructing your alter or drop statement based on pg_class.relkind.