Weird constraint output
[Please hold me on the to:/cc: list since I am not subscribed]
After talking this over with some of the great guys on IRC it was
suggested I ask here.
I am currently working on a document about how to convert from MySQL to
PostgreSQL (Sybase, Oracle, DB2, MS SQL Server are also going to be
done). I am now messing with CONSTRAINT CHECK and am encountering
something I don't see the logic of.
I decided to make sure Bugzilla works on pgsql 7.3.x and started to
convert the MySQL database schema to a pgsql one. One of the parts has
a layout like:
CREATE TABLE bugs (
-- ...skipping to relevant point
bug_severity character varying(50) -- in MySQL this is enum()
);
Next I did an:
ALTER TABLE ONLY bugs ADD CONSTRAINT bugs_severity_cstr CHECK
(bug_severity IN ('blocker', 'critical', 'major'));
Now, when I do a \d bugs I get:
Check constraints: "bugs_severity_cstr" (((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying))
I would've expected:
Check constraints: "bugs_severity_cstr" ((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying) OR (bug_severity = 'major'::character varying))
If you have even more choices there (as Bugzilla does) you even get:
CONSTRAINT bugs_severity_cstr CHECK ((((((((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying)) OR
(bug_severity = 'normal'::character varying)) OR (bug_severity =
'minor'::character varying)) OR (bug_severity = 'trivial'::character
varying)) OR (bug_severity = 'enhancement'::character varying)))
);
But there is no logic to have all those parens plus it makes pg_dump -s
and \d tablename a whole lot messier to read.
Can anyone clarify why we have it like this? Or whether or not it is a
bug perhaps? I could understand micro optimizations, but in this case?
Thanks,
--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
One moon shows in every pool; in every pool, the one moon...
Jeroen Ruigrok/asmodai wrote:
Check constraints: "bugs_severity_cstr" ((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying) OR (bug_severity = 'major'::character varying))If you have even more choices there (as Bugzilla does) you even get:
CONSTRAINT bugs_severity_cstr CHECK ((((((((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying)) OR
(bug_severity = 'normal'::character varying)) OR (bug_severity =
'minor'::character varying)) OR (bug_severity = 'trivial'::character
varying)) OR (bug_severity = 'enhancement'::character varying)))
);But there is no logic to have all those parens plus it makes pg_dump -s
and \d tablename a whole lot messier to read.Can anyone clarify why we have it like this? Or whether or not it is a
bug perhaps? I could understand micro optimizations, but in this case?
Obviously psql uses either use pg_constraint.consrc or
pg_get_expr(conbin, conrelid) which both will give this lot of
parentheses, so it's not a bug, but a feature
For easier reengineering, I invented several pg_get_xxx functions with a
pretty-print option, which omits this messy parentheses, because I
noticed how ugly this looks while writing pgAdmin3 (especially for large
views with many joins and big expressions).
There have been suggestions to use the pretty-print options for psql (I
don't know which have been realized so far), and very harsh rejects
doing this in pg_dump, namely from Tom).
Regards,
Andreas
Obviously psql uses either use pg_constraint.consrc or
pg_get_expr(conbin, conrelid) which both will give this lot of
parentheses, so it's not a bug, but a feature
For easier reengineering, I invented several pg_get_xxx functions with a
pretty-print option, which omits this messy parentheses, because I
noticed how ugly this looks while writing pgAdmin3 (especially for large
views with many joins and big expressions).
There have been suggestions to use the pretty-print options for psql (I
don't know which have been realized so far), and very harsh rejects
doing this in pg_dump, namely from Tom).
I have changed psql to use pg_get_viewdef(oid, true). I agree with Tom for
not using it in dumps just yet though. Is there a function for getting nice
constraint defs?
Chris
Christopher Kings-Lynne wrote:
I have changed psql to use pg_get_viewdef(oid, true). I agree with Tom for
not using it in dumps just yet though.
While there still might be a pg_dump option to do this.
Is there a function for getting nice constraint defs?
Of course there is, use pg_get_constraintdef(text, bool) for this.
pg_get_constraintdef(text, bool)
pg_get_expr(text, oid, bool)
pg_get_indexdef(text, int4, bool)
pg_get_ruledef(text, bool)
pg_get_viewdef(text, bool)
all have the pretty-print bool. Note that pg_get_indexdef's second
parameter selects if the complete definition is returned (=0) or only
that column's definition.
Regards,
Andreas