Re: Check constraints.

On 03/27/2018 04:23 AM, Steve Rogerson wrote:
> I am trying to add/change a constraint programmatically, but not if it
> already exists, and is the same as before.
> ...
> Is there a way of getting the "normalised" version of constraint so decide if
> I need to update the constraint if one already exists?

Hi Steve,

I wrote a Ruby gem to do this some years ago. Here is the SQL I used:

SELECT c.conname,
t.relname,
pg_get_expr(c.conbin, c.conrelid)
FROM pg_catalog.pg_constraint c,
pg_catalog.pg_class t,
pg_catalog.pg_namespace n
WHERE c.contype = 'c'
AND c.conrelid = t.oid
AND t.relkind = 'r'
AND n.oid = t.relnamespace
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(t.oid)

https://github.com/pjungwir/db_leftovers/blob/master/lib/db_leftovers/postgres_database_interface.rb#L117-L137

I haven't used it against the last few Postgres versions, but it
probably still works or needs only minor adjustments.

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com