Re: OT postgres: bitwise testing & setting?
will trillich wrote:
>this is way OT, but debian-user has the Folks Who Know...
>
>i'm using 6.3 postgresql on potato, and would like to compress
>a wee bit-o-space out of each record by using bitwise booleans,
>instead of bytewise.
>
>there seems to be no bitwise operators to facilitate such
>testing (nor even an numerative dataset type) so i was trying
1. Is it actually worth jumping through hoops to save a few bytes?
Disk space is usually cheaper than time, and, to my mind, it is
of primary importance to keep database design as simple as
possible. These sort of tricks will make your life and any
successor maintainer's life more difficult in the future.
We aren't living in the 1960's, after all!
2. If you want to do fancy stuff, you're going to need to keep
PostgreSQL up to date. There are lots and lots of bugs in 6.3
It's definitely antediluvian! There are 7.0.3-2 debs available
for potato at http://www.samfundet.no/~tfheen/debian. Treatment
of views is greatly improved on 6.3.
3. You might find the BIT or VARBIT types of use, which have some logical
and shift operators available. They don't seem to be documented,
and there are bugs in them even at 7.0.3 (cured in 7.1).
Operators are:
bray=# select oprname,t1.typname as oprleft,t2.typname as oprright,oprcode from pg_operator as o, pg_type as t1, pg_type as t2 where oprleft in (select oid from pg_type where typname ~* 'bit') and o.oprleft = t1.oid and o.oprright = t2.oid;
oprname | oprleft | oprright | oprcode
---------+---------+----------+---------------
= | bit | bit | biteq
<> | bit | bit | bitne
< | bit | bit | bitlt
> | bit | bit | bitgt
<= | bit | bit | bitle
>= | bit | bit | bitge
& | bit | bit | bitand
| | bit | bit | bitor
# | bit | bit | bitxor
<< | bit | int4 | bitshiftleft
>> | bit | int4 | bitshiftright
|| | bit | bit | bitcat
= | varbit | varbit | varbiteq
<> | varbit | varbit | varbitne
< | varbit | varbit | varbitlt
> | varbit | varbit | varbitgt
<= | varbit | varbit | varbitle
>= | varbit | varbit | varbitge
(18 rows)
Bit constants are specified like this: B'011001'
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For a child will be born to us, a son will be given to
us; And the government will rest on His shoulders; And
His name will be called Wonderful Counsellor, Mighty
God, Eternal Father, Prince of Peace."
Isaiah 9:6
Reply to: