Thread: Bit Varying vs New Column

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?
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?
3. Does someone have a JDBC example. I've tried String, BYTE[], Blob, byte[] as the Java type and none seem to work.

Thanks
Barry 

On Mon, 4 Jul 2011 12:26:48 -0700, Barry Laffoon <gotobarry@gmail.com> wrote:



> 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?

If you really need speed, consider using BOOLEAN - It is all a matter of
"speed" definition: if you have tons of concurrent requests use BOOLEAN.

--

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