Skip site navigation (1) Skip section navigation (2)

Re: Bit Varying vs New Column

From: Vincent Ficet <jean-vincent(dot)ficet(at)bull(dot)net>
To: Barry Laffoon <gotobarry(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Bit Varying vs New Column
Date: 2011-07-05 11:01:26
Message-ID: 4E12EF06.3080507@bull.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

Barry Laffoon wrote:
> Hi There,
>
> I have a property on a web form that is multi-select so the property
> can have multiple values. Normally I'd create separate column or a
> separate row for each value. I am thinking of using the BIT VARYING()
> data type though and storing the selected values as a bit mask. So I
> have several related questions:
>
> 1. Any general comments on how this performs?
Very well. It's fast, scalable and extensible.
> 2. Does someone have an example of a query where clause with the
> values in the BIT VARYING column being compared to a bit mask?
CREATE FUNCTION update_controller_status(INET, BIGINT) RETURNS VOID
    AS $_$
DECLARE
    controller_ipaddr ALIAS FOR $1;
    controller_status ALIAS FOR $2;
    snmp_status BIT(64);
BEGIN

SELECT BIT_OR(b'1'::BIT(64) >> s.bitshift) INTO snmp_status
FROM status_threshold s
WHERE s.name = 'snmp_err' AND
      (controller_status::BIT(64) & (b'1'::BIT(64) >> s.bitshift)) <>
0::BIT(64);

IF snmp_status IS NULL THEN

   -- clear the 'snmp_err' bit on all controllers attached to this
chassis because the SNMP
   -- failover should be transparent.

   SELECT BIT_OR(b'1'::BIT(64) >> s.bitshift) INTO snmp_status
   FROM status_threshold s
   WHERE s.name = 'snmp_err' AND
     (controller_status::BIT(64) & (b'1'::BIT(64) >> s.bitshift)) =
0::BIT(64);

   UPDATE controller c SET status = c.status & ~snmp_status WHERE c.id IN
   (SELECT c.id FROM controller c WHERE c.ipaddr = controller_ipaddr);

ELSE [ .. ]
> 3. Does someone have a JDBC example. I've tried String, BYTE[], Blob,
> byte[] as the Java type and none seem to work.
I use a 64bit number (BIGINT) and cast it to BIT(64) in the above example.

Cheers,

Vincent


>
> Thanks
> Barry 


In response to

pgsql-novice by date

Next:From: Roy's EmailDate: 2011-07-05 17:38:42
Subject: Link error with VC++ linking to libpq.lib
Previous:From: Vincent FicetDate: 2011-07-05 07:13:18
Subject: Re: pg_advisory_locks in a multithreaded application context

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group