Thread: Single character bitfields
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?
Thanks
Andrew
--
___________________________________________
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 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 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 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 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