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
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