Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 565C79FB946; Wed, 11 Apr 2007 19:01:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 47754-05; Wed, 11 Apr 2007 19:01:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc1-s18.bay0.hotmail.com (bay0-omc1-s18.bay0.hotmail.com [65.54.246.90]) by postgresql.org (Postfix) with ESMTP id 524389FB941; Wed, 11 Apr 2007 19:01:09 -0300 (ADT) Received: from BAY131-W4 ([65.55.136.39]) by bay0-omc1-s18.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Wed, 11 Apr 2007 15:01:07 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_c063acbb-4e48-4d25-b726-bfc344d9fba0_" X-Originating-IP: [67.116.31.14] From: Karthikeyan Sundaram To: Joe CC: , Subject: Re: [SQL] Urgent help in bit_string data type Date: Wed, 11 Apr 2007 15:01:07 -0700 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 11 Apr 2007 22:01:07.0476 (UTC) FILETIME=[E817F540:01C77C84] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.273 tagged_above=0 required=5 tests=AWL=-0.526, BAYES_05=-1.11, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200704/105 X-Sequence-Number: 25169 --_c063acbb-4e48-4d25-b726-bfc344d9fba0_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Joe, =20 Here is my next question. =20 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 in= sert to test_vwdo insteadinsert into test_a (b) values (COALESCE(new.b1::bi= t,'1')||COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit); ERROR: cannot cast type numeric to bit =20 How will I resolve this? =20 Regards skarthi > Date: Wed, 11 Apr 2007 17:44:01 -0400> From: dev@freedomcircle.net> Subje= ct: Re: [ADMIN] [SQL] Urgent help in bit_string data type> To: skarthi98@ho= tmail.com> CC: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org> > Hi s= karthi,> > 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 o= f type bit but expression is of type> > text> > HINT: You will need to rewr= ite 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=3Dflexicon_ wlmemailtagli= nemarch07= --_c063acbb-4e48-4d25-b726-bfc344d9fba0_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 repl= ace rule test_a_ins as on insert to test_vw
do instead
insert into te= st_a (b) values (COALESCE(new.b1::bit,'1')||COALESCE(new.b2::bit,'0')||COAL= ESCE(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@freedomcir= cle.net
> Subject: Re: [ADMIN] [SQL] Urgent help in bit_string data t= ype
> To: skarthi98@hotmail.com
> CC: pgsql-admin@postgresql.or= g; pgsql-sql@postgresql.org
>
> Hi skarthi,
>
> O= n Wed, 2007-04-11 at 13:30 -0700, Karthikeyan Sundaram wrote:
> > = insert into test_a values (to_char(1,'9'));
> >
> > ERRO= R: column "b" is of type bit but expression is of type
> > text> > HINT: You will need to rewrite or cast the expression.
> <= BR>> As suggested by the error, you should use a cast, e.g.,
> > insert into test_a values 9::bit(3);
>
> This will resul= t in binary '001' being inserted because you need 4 bits
> to represe= nt decimal 9.
>
> Joe
>
>
> --------------= -------------(end of broadcast)---------------------------
> TIP 6: e= xplain analyze is your friend

Take a break and play cros= sword puzzles - FREE! Play Now! = --_c063acbb-4e48-4d25-b726-bfc344d9fba0_--