Thread: Re: [SQL] Urgent help in bit_string data type

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_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?
 
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! Play Now!

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