Re: Boolean storage takes up 1 byte?

On Thu, Oct 01, 2009 at 11:37:40AM +0100, Thom Brown wrote:
> I've read the PostgreSQL documentation page on the boolean datatype (
> http://www.postgresql.org/docs/8.4/static/datatype-boolean.html) to find out
> what PostgreSQL's definition of a boolean is, as I believe it is distinctive
> from a bit(1) datatype

Yup, they're really for different things. AND, OR and NOT are defined
for the BOOLEAN datatype and not for bit strings.

> (as you can't max() a boolean.. not sure what an
> efficient alternative to that is).

bool_or and bool_and are aggregates that work over boolean data types.

> However, I see that a boolean takes up 1
> byte of storage, which is 8 bits. Is this due to the fact that the value
> can be null?

I believe it's more to do with the fact that if you add a boolean column
and then subsequently an int column then you're going to struggle to
"pack" them efficiently. PG always puts columns on the "end" so that you
can add a column in constant time (i.e. no need to rewrite the table
in some common situations). Once you start doing this then packing is
awkward and a single byte becomes much easier. Whether the value is
NULL is stored elsewhere in the row.

Yes, this could be made more efficient; whether it's worth it is a
difficult question!

> And does its storage as a byte affect indexing or query planning?

Not sure which aspects you're referring to here, sorry.

--
Sam http://samason.me.uk/