Single character bitfields

On Thu, 21 Aug 2008 12:40:29 +1000
"Andrew Maclean" <andrew.amaclean@gmail.com> wrote:

I have a large database and I want to have several fields (among many)
that are single character fields (in fact they are bitfields).

1) Is char(1) the most efficient way to store these fields? If not
what is better?

boolean?

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Thu, Aug 21, 2008 at 12:56 PM, Joshua Drake <jd@commandprompt.com> wrote:

On Thu, 21 Aug 2008 12:40:29 +1000
"Andrew Maclean" <andrew.amaclean@gmail.com> wrote:

I have a large database and I want to have several fields (among many)
that are single character fields (in fact they are bitfields).

1) Is char(1) the most efficient way to store these fields? If not
what is better?

boolean?

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

I wouldn't be happy doing this because I prefer treating boolean data
types as only TRUE or FALSE.

Is char(1) one byte in size?

--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________

On Aug 20, 2008, at 7:56 PM, Joshua Drake wrote:

I have a large database and I want to have several fields (among
many)
that are single character fields (in fact they are bitfields).

boolean?

Out of curiosity, does postgres collapse multiple boolean columns to a
bitfield internally? In other words, I guess I assume a single boolean
column will always take 1 byte of space... but if I have 8 boolean
columns, will they all fit into that 1 byte?

Ben <bench@silentmedia.com> writes:

Out of curiosity, does postgres collapse multiple boolean columns to a
bitfield internally?

No.

regards, tom lane

On 2008-08-21 05:29, Andrew Maclean wrote:

Is char(1) one byte in size?

No. It will also depend on database encoding, etc.

I think you should go with smallint, which is exactly 2 bytes. You'll
have 15 bits of storage (16 if you'd want to implement the special case
of minus sign).

IMHO the only smaller field are:
- one byte boolean, but it can only store 1 bit.
- one byte "char" (with quotes), but it is a non standard, integral
type, will cause interface problems and I don't know if it will not be
deprecated some time.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

In response to "Andrew Maclean" <andrew.amaclean@gmail.com>:

I have a large database and I want to have several fields (among many)
that are single character fields (in fact they are bitfields).

1) Is char(1) the most efficient way to store these fields? If not
what is better?
2) I need to test the field against constants, e.g if the field is
called source then I need tests like:
IF source = 0x10 THEN ...
This does not work in plpgsql functions (source is character(1)),
so what is the solution?

I'm not sure I understand what you're trying to accomplish, but there's
a bit string type:
http://www.postgresql.org/docs/8.3/static/datatype-bit.html

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

-----Original Message-----
From: Tomasz Ostrowski [mailto:tometzky@batory.org.pl]
Sent: Thursday, 21 August 2008 18:46
To: a.maclean@cas.edu.au
Cc: General
Subject: Re: Single character bitfields

On 2008-08-21 05:29, Andrew Maclean wrote:

Is char(1) one byte in size?

No. It will also depend on database encoding, etc.

I think you should go with smallint, which is exactly 2 bytes. You'll
have 15 bits of storage (16 if you'd want to implement the special case
of minus sign).

IMHO the only smaller field are:
- one byte boolean, but it can only store 1 bit.
- one byte "char" (with quotes), but it is a non standard, integral
type, will cause interface problems and I don't know if it will not be
deprecated some time.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

Thankyou for this, after reading around I have also come to the same
conclusion that smallint is the better option.

Andrew

-----Original Message-----
From: Bill Moran [mailto:wmoran@collaborativefusion.com]
Sent: Thursday, 21 August 2008 22:36
To: a.maclean@cas.edu.au
Cc: Andrew Maclean; General
Subject: Re: [GENERAL] Single character bitfields

In response to "Andrew Maclean" <andrew.amaclean@gmail.com>:

I have a large database and I want to have several fields (among many)
that are single character fields (in fact they are bitfields).

1) Is char(1) the most efficient way to store these fields? If not
what is better?
2) I need to test the field against constants, e.g if the field is
called source then I need tests like:
IF source = 0x10 THEN ...
This does not work in plpgsql functions (source is character(1)),
so what is the solution?

I'm not sure I understand what you're trying to accomplish, but there's
a bit string type:
http://www.postgresql.org/docs/8.3/static/datatype-bit.html

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Thankyou for your input. I have decided to use smallint for various reasons.

On Aug 21, 2008, at 3:45 AM, Tomasz Ostrowski wrote:

- one byte "char" (with quotes), but it is a non standard, integral
type, will cause interface problems and I don't know if it will not be
deprecated some time.

It's used in the catalogs, so I'd say the odds of it getting replaced
anytime in the near future (if ever) are next to zero, especially
considering "it ain't broke, so don't fix it" and that there's
undoubtedly people using it in the wild.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828