pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUrgent help in bit_string data type
6+ messages / 2 participants
[nested] [flat]
* Urgent help in bit_string data type
@ 2007-04-11 20:30 Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-11 21:44 ` Re: [SQL] Urgent help in bit_string data type Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Karthikeyan Sundaram @ 2007-04-11 20:30 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-sql
Hi Gurus,
I have a table with datatype as bitstrings
create table test_a (b bit(3));
insert into test_a values ('111');
This will convert a bit to a number
==========================
select to_number((substring(b,1,1)::int),9)+3 from test_a;
How will I convert a Number to a bit
----------------------------------
insert into test_a values (to_char(1,'9'));
ERROR: column "b" is of type bit but expression is of type textHINT: You will need to rewrite or cast the expression.
Please help me
regards
skarthi
_________________________________________________________________
i'm making a difference. Make every IM count for the cause of your choice. Join Now.
http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=http://im.live.com/messenger/im/ho...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Urgent help in bit_string data type
2007-04-11 20:30 Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-11 21:44 ` Joe <dev@freedomcircle.net>
0 siblings, 0 replies; 6+ messages in thread
From: Joe @ 2007-04-11 21:44 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
Hi skarthi,
On Wed, 2007-04-11 at 13:30 -0700, Karthikeyan Sundaram wrote:
> insert into test_a values (to_char(1,'9'));
>
> ERROR: column "b" is of type bit but expression is of type
> text
> HINT: You will need to rewrite or cast the expression.
As suggested by the error, you should use a cast, e.g.,
insert into test_a values 9::bit(3);
This will result in binary '001' being inserted because you need 4 bits
to represent decimal 9.
Joe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Urgent help in bit_string data type
@ 2007-04-11 22:01 Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-11 23:00 ` Re: [SQL] Urgent help in bit_string data type Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Karthikeyan Sundaram @ 2007-04-11 22:01 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
Thanks Joe,
Here is my next question.
create table test_a (b bit(3));
create view test_vw (b1, b2, b3)as select to_number(substring(b,1,1)::int,'9') as b1,to_number(substring(b,2,1)::int,'9') as b2,to_number(substring(b,3,1)::int,'9') as b3 from test_a;create or replace rule test_a_ins as on insert to test_vwdo insteadinsert into test_a (b) values (COALESCE(new.b1::bit,'1')||COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit);
ERROR: cannot cast type numeric to bit
How will I resolve this?
Regards
skarthi
> Date: Wed, 11 Apr 2007 17:44:01 -0400> From: dev@freedomcircle.net> Subject: Re: [ADMIN] [SQL] Urgent help in bit_string data type> To: skarthi98@hotmail.com> CC: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org> > Hi skarthi,> > On Wed, 2007-04-11 at 13:30 -0700, Karthikeyan Sundaram wrote:> > insert into test_a values (to_char(1,'9'));> > > > ERROR: column "b" is of type bit but expression is of type> > text> > HINT: You will need to rewrite or cast the expression.> > As suggested by the error, you should use a cast, e.g.,> > insert into test_a values 9::bit(3);> > This will result in binary '001' being inserted because you need 4 bits> to represent decimal 9.> > Joe> > > ---------------------------(end of broadcast)---------------------------> TIP 6: explain analyze is your friend
_________________________________________________________________
Take a break and play crossword puzzles - FREE!
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Urgent help in bit_string data type
2007-04-11 22:01 Re: [SQL] Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-11 23:00 ` Joe <dev@freedomcircle.net>
0 siblings, 0 replies; 6+ messages in thread
From: Joe @ 2007-04-11 23:00 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
Hi skarthi,
On Wed, 2007-04-11 at 15:01 -0700, Karthikeyan Sundaram wrote:
> create table test_a (b bit(3));
>
> create view test_vw (b1, b2, b3)
> as select
> to_number(substring(b,1,1)::int,'9') as b1,
> to_number(substring(b,2,1)::int,'9') as b2,
> to_number(substring(b,3,1)::int,'9') as b3 from test_a;
>
> create or replace rule test_a_ins as on insert to test_vw
> do instead
> insert into test_a (b) values (COALESCE(new.b1::bit,'1')||
> COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit);
>
> ERROR: cannot cast type numeric to bit
>
> How will I resolve this?
*My* question is why are you doing such convoluted conversions, from bit
string to text, then to int, etc.? It seems to me like you want to
manipulate bits and if that's the case, you should be using the bit
string operators, as someone pointed out a couple of days ago. In case
you haven't looked at them, please see:
http://www.postgresql.org/docs/8.2/static/functions-bitstring.html
Joe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Urgent help in bit_string data type
@ 2007-04-11 23:29 Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-12 04:07 ` Re: [SQL] Urgent help in bit_string data type Joe <dev@freedomcircle.net>
0 siblings, 1 reply; 6+ messages in thread
From: Karthikeyan Sundaram @ 2007-04-11 23:29 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
Joe,
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.
Regards
skarthi> Date: Wed, 11 Apr 2007 19:00:23 -0400> From: dev@freedomcircle.net> Subject: Re: [ADMIN] [SQL] Urgent help in bit_string data type> To: skarthi98@hotmail.com> CC: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org> > Hi skarthi,> > On Wed, 2007-04-11 at 15:01 -0700, Karthikeyan Sundaram wrote:> > create table test_a (b bit(3));> > > > create view test_vw (b1, b2, b3)> > as select > > to_number(substring(b,1,1)::int,'9') as b1,> > to_number(substring(b,2,1)::int,'9') as b2,> > to_number(substring(b,3,1)::int,'9') as b3 from test_a;> > > > create or replace rule test_a_ins as on insert to test_vw> > do instead> > insert into test_a (b) values (COALESCE(new.b1::bit,'1')||> > COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit);> > > > ERROR: cannot cast type numeric to bit> > > > How will I resolve this?> > *My* question is why are you doing such convoluted conversions, from bit> string to text, then to int, etc.? It seems to me like you want to> manipulate bits and if that's the case, you should be using the bit> string operators, as someone pointed out a couple of days ago. In case> you haven't looked at them, please see:> > http://www.postgresql.org/docs/8.2/static/functions-bitstring.html; > Joe> > > ---------------------------(end of broadcast)---------------------------> TIP 6: explain analyze is your friend
_________________________________________________________________
Live Search Maps – find all the local information you need, right when you need it.
http://maps.live.com/?icid=wlmtag2&FORM=MGAC01=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Urgent help in bit_string data type
2007-04-11 23:29 Re: [SQL] Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-12 04:07 ` Joe <dev@freedomcircle.net>
0 siblings, 0 replies; 6+ messages in thread
From: Joe @ 2007-04-12 04:07 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-12 04:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-11 20:30 Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-11 21:44 ` Joe <dev@freedomcircle.net>
2007-04-11 22:01 Re: [SQL] Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-11 23:00 ` Joe <dev@freedomcircle.net>
2007-04-11 23:29 Re: [SQL] Urgent help in bit_string data type Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-12 04:07 ` Joe <dev@freedomcircle.net>
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