Re: OT postgres: bitwise testing & setting?
On Thu, Dec 21, 2000 at 09:41:54PM +0000, Oliver Elphick wrote:
> 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?
2 bytes x 2,000,000 records = 4mb
16 bytes x 2,000,000 records = 32mb!
> 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!
true. trade-offs, trade-offs. hmm...
> 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.
not postgredeluvian? :)
thanks for the link! (i'm beginning to think that oliver IS
postgresql...)
> 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'
cool. thanks!
--
There are only two places in the world where time takes
precedence over the job to be done. School and prison.
--William Glasser
will@serensoft.com *** http://www.dontUthink.com/
volunteer to document your experience for next week's
newbies -- http://www.eGroups.com/messages/newbieDoc
Reply to: