pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
table constraint on two columns 4+ messages / 3 participants [nested] [flat]
* table constraint on two columns @ 2013-07-22 14:05 ldrlj1 <russelljanusz@yahoo.com> 2013-07-22 14:16 ` Re: table constraint on two columns Vik Fearing <vik.fearing@dalibo.com> 0 siblings, 1 reply; 4+ messages in thread From: ldrlj1 @ 2013-07-22 14:05 UTC (permalink / raw) To: pgsql-sql Postgres 9.2.4. I have two columns, approved and comments. Approved is a boolean with no default value and comments is a character varying (255) and nullable. I am trying to create a constraint that will not allow a row to be entered if approved is set to false and comments is null. This does not work. yada, yada, yada... add constraint "chk_comments' check (approved = false and comments is not null). The constraint is successfully added, but does not work as I expected. -- View this message in context: http://postgresql.1045698.n5.nabble.com/table-constraint-on-two-columns-tp5764645.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table constraint on two columns 2013-07-22 14:05 table constraint on two columns ldrlj1 <russelljanusz@yahoo.com> @ 2013-07-22 14:16 ` Vik Fearing <vik.fearing@dalibo.com> 2013-07-22 14:44 ` Re: table constraint on two columns Adrian Klaver <adrian.klaver@gmail.com> 2013-07-22 14:53 ` Re: table constraint on two columns ldrlj1 <russelljanusz@yahoo.com> 0 siblings, 2 replies; 4+ messages in thread From: Vik Fearing @ 2013-07-22 14:16 UTC (permalink / raw) To: ldrlj1 <russelljanusz@yahoo.com>; +Cc: pgsql-sql On 07/22/2013 04:05 PM, ldrlj1 wrote: > Postgres 9.2.4. > > I have two columns, approved and comments. Approved is a boolean with no > default value and comments is a character varying (255) and nullable. > > I am trying to create a constraint that will not allow a row to be entered > if approved is set to false and comments is null. CHECK constraints work on positives, so restate your condition that way. A row is permissible if approved is true or the comments are not null, correct? So... ...add constraint chk_comments (approved or comments is not null)... > This does not work. yada, yada, yada... add constraint "chk_comments' check > (approved = false and comments is not null). The constraint is successfully > added, but does not work as I expected. That's not the same check as what you described. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table constraint on two columns 2013-07-22 14:05 table constraint on two columns ldrlj1 <russelljanusz@yahoo.com> 2013-07-22 14:16 ` Re: table constraint on two columns Vik Fearing <vik.fearing@dalibo.com> @ 2013-07-22 14:44 ` Adrian Klaver <adrian.klaver@gmail.com> 1 sibling, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2013-07-22 14:44 UTC (permalink / raw) To: Vik Fearing <vik.fearing@dalibo.com>; +Cc: ldrlj1 <russelljanusz@yahoo.com>; pgsql-sql On 07/22/2013 07:16 AM, Vik Fearing wrote: > On 07/22/2013 04:05 PM, ldrlj1 wrote: >> Postgres 9.2.4. >> >> I have two columns, approved and comments. Approved is a boolean with no >> default value and comments is a character varying (255) and nullable. >> >> I am trying to create a constraint that will not allow a row to be entered >> if approved is set to false and comments is null. > > CHECK constraints work on positives, so restate your condition that > way. A row is permissible if approved is true or the comments are not > null, correct? So... > > ...add constraint chk_comments (approved or comments is not null)... > >> This does not work. yada, yada, yada... add constraint "chk_comments' check >> (approved = false and comments is not null). The constraint is successfully >> added, but does not work as I expected. > > That's not the same check as what you described. An additional comment, did you put the check constraint on a column or the table? From the docs: http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html: .. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns... > > -- Adrian Klaver adrian.klaver@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: table constraint on two columns 2013-07-22 14:05 table constraint on two columns ldrlj1 <russelljanusz@yahoo.com> 2013-07-22 14:16 ` Re: table constraint on two columns Vik Fearing <vik.fearing@dalibo.com> @ 2013-07-22 14:53 ` ldrlj1 <russelljanusz@yahoo.com> 1 sibling, 0 replies; 4+ messages in thread From: ldrlj1 @ 2013-07-22 14:53 UTC (permalink / raw) To: pgsql-sql DOH! Complete brain fart. Thank you for re-educating me :)! Worked like a charm. -- View this message in context: http://postgresql.1045698.n5.nabble.com/table-constraint-on-two-columns-tp5764645p5764649.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-07-22 14:53 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-07-22 14:05 table constraint on two columns ldrlj1 <russelljanusz@yahoo.com> 2013-07-22 14:16 ` Vik Fearing <vik.fearing@dalibo.com> 2013-07-22 14:44 ` Adrian Klaver <adrian.klaver@gmail.com> 2013-07-22 14:53 ` ldrlj1 <russelljanusz@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox