pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do i extract a certain bit from a bigint column
4+ messages / 4 participants
[nested] [flat]
* How do i extract a certain bit from a bigint column
@ 2004-05-12 11:05 Mats Sjöberg <mats.sjoberg@cybernetics.se>
0 siblings, 2 replies; 4+ messages in thread
From: Mats Sjöberg @ 2004-05-12 11:05 UTC (permalink / raw)
To: pgsql-sql
Hello everyone
In a table i have a column status of type bigint.
I need to create a view of the table including all rows with bit 4 set (value 8).
At the same time i need to exclude excludig all rows with bit 2 set.
What is the syntax to extract those bits?
I have tested get_bit(string, offset) but this requires a string, and not a bigint.
Is there a function to
Best regards, Mats
Attachments:
Mats Sjöberg.vcf (text/x-vcard, 376B) download
BEGIN:VCARD
VERSION:2.1
N:Sjöberg;Mats
FN:Mats Sjöberg
ORG:Cybernetics Business Decisions AB
TEL;WORK;VOICE:3921
TEL;CELL;VOICE:0706 54 07 68
ADR;WORK:;;Rännilstäppan;ÅKERSBERGA;;184 91;SWEDEN
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:R=E4nnilst=E4ppan=0D=0A=C5KERSBERGA 184 91=0D=0ASWEDEN
EMAIL;PREF;INTERNET:mats.sjoberg@cybernetics.se
REV:20040302T073452Z
END:VCARD
* Re: How do i extract a certain bit from a bigint column
@ 2004-05-12 12:17 Mike Rylander <miker@purplefrog.com>
parent: Mats Sjöberg <mats.sjoberg@cybernetics.se>
1 sibling, 1 reply; 4+ messages in thread
From: Mike Rylander @ 2004-05-12 12:17 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mats Sjöberg <mats.sjoberg@cybernetics.se>
On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote:
> Hello everyone
> In a table i have a column status of type bigint.
> I need to create a view of the table including all rows with bit 4 set
> (value 8).
> At the same time i need to exclude excludig all rows with bit 2 set.
>
> What is the syntax to extract those bits?
> I have tested get_bit(string, offset) but this requires a string, and not a
> bigint.
> Is there a function to
The easiest way is to test for a bit using bitwise and:
SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) =
0;
-miker
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How do i extract a certain bit from a bigint column
@ 2004-05-12 12:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mats Sjöberg <mats.sjoberg@cybernetics.se>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-05-12 12:55 UTC (permalink / raw)
To: Mats Sjöberg <mats.sjoberg@cybernetics.se>; +Cc: pgsql-sql
=?iso-8859-1?Q?Mats_Sj=F6berg?= <mats.sjoberg@cybernetics.se> writes:
> What is the syntax to extract those bits?
At least in 7.4, you can do it the same way you'd do it in C:
regression=# select ((47::bigint) >> 3) & 1;
?column?
----------
1
(1 row)
I'm not sure when the bigint >> and & operators got added, but \do would
tell you quickly enough if they're in your version.
Mind you that this is not going to be an especially fast solution, since
these are not indexable operators. You might be better advised to
rethink your data representation.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How do i extract a certain bit from a bigint column
@ 2004-05-12 18:16 Bruno Wolff III <bruno@wolff.to>
parent: Mike Rylander <miker@purplefrog.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-05-12 18:16 UTC (permalink / raw)
To: Mike Rylander <miker@purplefrog.com>; +Cc: pgsql-sql@postgresql.org, Mats Sjöberg <mats.sjoberg@cybernetics.se>
On Wed, May 12, 2004 at 08:17:39 -0400,
Mike Rylander <miker@purplefrog.com> wrote:
>
> The easiest way is to test for a bit using bitwise and:
>
> SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) =
> 0;
The following will probably be a bit faster:
SELECT * FROM table WHERE (status & 10::BIGINT) = 8;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-05-12 18:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-12 11:05 How do i extract a certain bit from a bigint column Mats Sjöberg <mats.sjoberg@cybernetics.se>
2004-05-12 12:17 ` Mike Rylander <miker@purplefrog.com>
2004-05-12 18:16 ` Bruno Wolff III <bruno@wolff.to>
2004-05-12 12:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox