Обсуждение: 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