Message-ID: <43100A51.1040602@travelamericas.com>
Date: Fri, 26 Aug 2005 23:38:09 -0700
From: Chris Travers <chris@travelamericas.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: "Matt L." <survivedsushi@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: booleans and nulls
References: <20050821022557.21501.qmail@web35204.mail.mud.yahoo.com>
In-Reply-To: <20050821022557.21501.qmail@web35204.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matt L. wrote:

>Out of curiousity, 
>
>1. Does a boolean column occupy 1byte of disk whether
>or not the value is null or not? 
>  
>
I believe so.

>2. Is matching on IS NULL or = 0 more efficient? 
>
>  
>
Hmm... = 0 is the same as IS FALSE.  Not the same as IS NULL.  So I 
guess it is apples v. oranges....

>3. If I ix'd columns w/ null does postgres know
>whatevers not indexed is null or would their be no
>point?
>  
>
>I currently utilize null fields as 'not a value' has
>meaning in a program i've been working on as I don't
>want to put false in every column when i only need a
>couple with a true/false value. 
>
>I'm not joining tables on NULLS, just filtering w/
>them. 
>  
>
Sounds like a partial index would be your best bet.  Something like:
CREATE index ON my_table WHERE my_bool IS NOT NULL

Best Wishes,
Chris Travers
Metatron Technology Consulting