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@illuminatedcomputing.com