From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] Indexing on a boolean field? |
Date: | 1998-06-15 16:17:18 |
Message-ID: | l03110703b1aaf74c8755@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marcio Macedo | 1998-06-15 17:51:27 | Binary large objects |
Previous Message | Tom Lane | 1998-06-15 15:33:16 | Indexing on a boolean field? |