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)
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