While working in jOOQ-agnostic (without code generation) migrations I've encountered a situation in which I need to check if a constraint (unique, foreign key) already exists in the database in order to complete further operations.
What I've tried so far is to run the drop and try to catch the exception, but it fails the transaction and stops following migrations from happening
dsl.alterTable(table).dropConstraint(constraintName).execute();
...
>> ERROR: constraint "t_client_name_unique" of relation "t_client" does not exist
Setup:
For jOOQ 3.13+, we're investing heavily in supporting more such migration scenarios. In the future, we'll support some vendor agnostic information_schema style views that produce this kind of meta information for all databases: #8301
Another feature that could help you here immediately would be native DROP CONSTRAINT IF EXISTS support: #9557. You could, of course, use plain SQL to run this particular statement on PostgreSQL, until #9557 is available
Alternatively, in your case, since you're only using PostgreSQL, you could do this directly by querying PostgreSQL's information_schema. You could generate information_schema tables and then run this query:
select *
from information_schema.table_constraints
where constraint_schema = :constraint_schema
and constraint_name = :constraint_name