pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
booleans and nulls 3+ messages / 3 participants [nested] [flat]
* booleans and nulls @ 2005-08-21 02:25 Matt L. <survivedsushi@yahoo.com> 2005-08-27 06:38 ` Re: booleans and nulls Chris Travers <chris@travelamericas.com> 2005-08-30 19:47 ` Re: booleans and nulls Scott Marlowe <smarlowe@g2switchworks.com> 0 siblings, 2 replies; 3+ messages in thread From: Matt L. @ 2005-08-21 02:25 UTC (permalink / raw) To: pgsql-sql Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? 2. Is matching on IS NULL or = 0 more efficient? 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. Thanks __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: booleans and nulls 2005-08-21 02:25 booleans and nulls Matt L. <survivedsushi@yahoo.com> @ 2005-08-27 06:38 ` Chris Travers <chris@travelamericas.com> 1 sibling, 0 replies; 3+ messages in thread From: Chris Travers @ 2005-08-27 06:38 UTC (permalink / raw) To: Matt L. <survivedsushi@yahoo.com>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: booleans and nulls 2005-08-21 02:25 booleans and nulls Matt L. <survivedsushi@yahoo.com> @ 2005-08-30 19:47 ` Scott Marlowe <smarlowe@g2switchworks.com> 1 sibling, 0 replies; 3+ messages in thread From: Scott Marlowe @ 2005-08-30 19:47 UTC (permalink / raw) To: Matt L. <survivedsushi@yahoo.com>; +Cc: pgsql-sql On Sat, 2005-08-20 at 21:25, Matt L. wrote: > Out of curiousity, > > 1. Does a boolean column occupy 1byte of disk whether > or not the value is null or not? No. Nulls are stored, one bit per, to a byte at a time. I.e. if you have 8 null fields, they are stored in the same byte. > 2. Is matching on IS NULL or = 0 more efficient? Generally, =0 is EASIER to implement. This is because IS NULL is not directly indexable. At least it wasn't in the past. 8.0 or 8.1 may have made it so. The reason for this is that indexing requires a list of operators, and IS NULL isn't really an operator, but syntax, so it doesn't map to an operator like = < > >= etc... However, you can index on partials, so it was possible to make an index that was like this: create index a_dx on a (boofield) where boolfield IS NULL and then IS NULL was indexed. But at the cost of an additional index to maintain. OTOH, if most of the fields are not null, and the occasional null is what you're looking for, then it's a good idea. If you've got 50/50 distribution of nulls and not nulls, indexing on nulls makes no sense, since you'll never actually use the index because it will always be cheaper to pull by seq scan, except in some clustered index situs. > 3. If I ix'd columns w/ null does postgres know > whatevers not indexed is null or would their be no > point? Actually, it indexes the nulls, it just has a hard time using the index due to the above operator mapping issue. To use the index with NULL / NOT NULL requires the above mentioned partial index. > 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. That's a good idea, as it saves space as well. That's pretty much what NULL was meant for. > I'm not joining tables on NULLS, just filtering w/ > them. Then look at the index / where IS (NOT) NULL solution, and let us know how that works. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-30 19:47 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-08-21 02:25 booleans and nulls Matt L. <survivedsushi@yahoo.com> 2005-08-27 06:38 ` Chris Travers <chris@travelamericas.com> 2005-08-30 19:47 ` Scott Marlowe <smarlowe@g2switchworks.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