pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Indexing on a boolean field?
Date: Mon, 15 Jun 1998 19:17:18 +0300
Message-ID: <l03110703b1aaf74c8755@[147.233.159.109]> (raw)
In-Reply-To: <1691.897924796@sss.pgh.pa.us>

At 18:33 +0300 on 15/6/98, Tom Lane wrote:


> I want to be able to select out the elements of a table that have
> a TRUE value in a boolean field "flag".  (Basically the flag means
> the record hasn't been processed yet.)  I can do something like
>
> 	SELECT * FROM table WHERE flag = 't';

I know I'm nitpicking, but I think "WHERE flag" is more elegant. I have an
aversion to comparing to boolean literals. Flag = 'true' is equivalent to
Flag, and Flag = 'false' is equivalent to NOT Flag in every language I know.

> I thought of creating an index on the flag field, but soon found that
> you can't do it in Postgres (there's no operator for index on boolean).
> In any case, I doubt that btree or hash indexes would work well with
> only two distinct data values.

I don't know that they won't work well. They will both probably reduce to
sequential scan on all the records with the same value - which is what you
need. I mean, as soon as you pick the bucket with all 'true' values and no
'false' values, you've made the saving you needed.

> Another possibility is to keep the not-yet-processed records in a
> separate table, but that seems pretty ugly as well; especially since
> I sometimes want to see both processed and unprocessed records.

Well, you could use a union to do that.

> Has anyone got some advice on how to approach this problem?

If you ask me, I'd just replace the boolean with a char flag. Less elegant,
perhaps (and would make your above query stay the way you wrote it...), but
since I don't think booleans take less space than a whole byte anyway, it's
as space-efficient as booleans, and it has its own index operator family.

In case you're worried about integrity, you can define a constraint on the
char field, making sure it doesn't enter anything except 'T' and 'F', or
't' and 'f', or whatever.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (2+ messages)

Message-ID: <l03110703b1aaf74c8755@[147.233.159.109]>
Permalink:  ../l03110703b1aaf74c8755@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110703b1aaf74c8755@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, tgl@sss.pgh.pa.us
  Subject: Re: [SQL] Indexing on a boolean field?
  In-Reply-To: <l03110703b1aaf74c8755@[147.233.159.109]>
  References: <1691.897924796@sss.pgh.pa.us> <l03110703b1aaf74c8755@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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