Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37386=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1V1HMG-0005Zy-W3
 for pgsql-sql@arkaria.postgresql.org; Mon, 22 Jul 2013 14:45:05 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37386=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1V1HMG-0006qK-Fx
 for pgsql-sql@arkaria.postgresql.org; Mon, 22 Jul 2013 14:45:04 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1V1HMF-0006qD-Bc
 for pgsql-sql@postgresql.org; Mon, 22 Jul 2013 14:45:03 +0000
Received: from mail-pd0-x234.google.com ([2607:f8b0:400e:c02::234])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1V1HM9-0004VQ-Sj
 for pgsql-sql@postgresql.org; Mon, 22 Jul 2013 14:45:02 +0000
Received: by mail-pd0-f180.google.com with SMTP id 10so6831198pdi.39
 for <pgsql-sql@postgresql.org>; Mon, 22 Jul 2013 07:44:56 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=RbpnsQLGPj1Ob1alJ7xKzV9RdA//BYrusrMhimUNqFc=;
 b=zVG8TiUJUA6KUW9XLOYtBLeNae9Hz036jWRyXKOb53Ew/OwcfqDzoFO5lDppqCRmrL
 0aMOehlOuT5KP84+d1O7hStoNS3L3nsvX7te4DhpIELBUUd4BJrud19dCZPlw1pOxiPL
 H1tKRHpAUrV1Ehja2LfbQ0Wa8dzCyNsD8W1fAZr0v6GdRiEjg6fIJV0h3KBAVGY0deWa
 3tIQhOxx/JX0PTeGXSpQ/fTAVKFoY8dh+ql486LFvAxrVLG9z05rYRxUTMHSHKEoTmEf
 29B5N8bX99n23yjBnX8TFLKiqtN8eRhnewunXB9f7NHeP/gO9jY0ufcFMQK0JFWN7/gY
 vKfg==
X-Received: by 10.66.40.212 with SMTP id z20mr22613205pak.51.1374504296668;
 Mon, 22 Jul 2013 07:44:56 -0700 (PDT)
Received: from [192.168.1.2] (174-21-183-190.tukw.qwest.net. [174.21.183.190])
 by mx.google.com with ESMTPSA id
 w8sm33860810pab.12.2013.07.22.07.44.55 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 22 Jul 2013 07:44:55 -0700 (PDT)
Message-ID: <51ED4566.8060005@gmail.com>
Date: Mon, 22 Jul 2013 07:44:54 -0700
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:17.0) Gecko/20130620 Thunderbird/17.0.7
MIME-Version: 1.0
To: Vik Fearing <vik.fearing@dalibo.com>
CC: ldrlj1 <russelljanusz@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: table constraint on two columns
References: <1374501921767-5764645.post@n5.nabble.com>
 <51ED3ECB.5020703@dalibo.com>
In-Reply-To: <51ED3ECB.5020703@dalibo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: 0.7 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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