Re: Bit Varying vs New Column

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