Обсуждение: Check constraints.

williamI am trying to add/change a constraint programmatically, but not if it
already exists, and is the same as before.


I can so something like (may not be precise ...)

select check_clause from information_schema.check_constraints where
constraint_name = 'my-name'

but this returns that clause in a normalised format that is not compatible
with the text I am trying to compare, so I'm adding something like ...

alter table my_table  add check (my_type = any (array['GOOD' , 'BAD']))

but the check_clause from above looks like ...

(((my_type)::text = ANY (ARRAY['GOOD'::text, 'BAD'::text])))

Is there a way of getting the "normalised" version of constraint so decide if
I need to update the constraint if one already exists?

Steve

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

On 27/03/18 15:44, Paul Jungwirth wrote:
>           SELECT  c.conname,
>  ...
This just does a variation on select * from
information_schema.check_constraints, and has the same issue, that is the the
returned value for the constraint is not what I give when I create it - but
some 'normalised' version of it.

Steve