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

Re: [SQL] Urgent help in bit_string data type

From: Joe <dev(at)freedomcircle(dot)net>
To: Karthikeyan Sundaram <skarthi98(at)hotmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Urgent help in bit_string data type
Date: 2007-04-12 04:07:07
Message-ID: 1176350827.782.37.camel@pampa (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
Hi skarthi,

On Wed, 2007-04-11 at 16:29 -0700, Karthikeyan Sundaram wrote:
>   The reason why I am asking is, we are building an interface layer
> where all our users will have a view.  They shouldn't know anything
> about how and where the data is stored in the table.  They can be seen
> only by the portal which will use view.
>  
>     That's the reason.

I can understand using views to hide data from users, but that was not
what I was asking about.  It seems that you still have not read the page
that we referenced.  Consider the following:

test=> create table test_a (b bit(3));
CREATE TABLE
test=> insert into test_a values (b'001');
INSERT 0 1
test=> insert into test_a values (b'010');
INSERT 0 1
test=> insert into test_a values (b'101');
INSERT 0 1
test=> select * from test_a;
  b  
-----
 001
 010
 101
(3 rows)

test=> create or replace view test_vw as
test-> select b::bit(1) as b2, (b<<1)::bit(1) as b1,
test-> (b<<2)::bit(1) as b0 from test_a;
CREATE VIEW
test=> select * from test_vw;
 b2 | b1 | b0 
----+----+----
 0  | 0  | 1
 0  | 1  | 0
 1  | 0  | 1
(3 rows)

The view above gives the same results as your original view, but only
uses bit manipulations (and the only counterintuitive part is ::bit(1)
gives you the MSB).  Your view has to convert a bit string to text (or
maybe bytea) for the substring function, then it has to convert the text
to int because of your explicit cast, and finally it has to convert back
to text for the to_number function.  The result of to_number is numeric
and you're trying to cast it to bit, which is what the ERROR was telling
you can't do.

Joe


In response to

pgsql-admin by date

Next:From: Phillip SmithDate: 2007-04-12 05:35:15
Subject: Re: Best compressed archive_command on Linux?
Previous:From: Robert TreatDate: 2007-04-12 01:20:42
Subject: Re: Advice on migration without down-time

pgsql-sql by date

Next:From: A. KretschmerDate: 2007-04-12 05:12:50
Subject: Re: Replace string
Previous:From: Karthikeyan SundaramDate: 2007-04-11 23:29:45
Subject: Re: [SQL] Urgent help in bit_string data type

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