Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 6CDF19FB4E0; Wed, 11 Apr 2007 20:29:51 -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 68913-05; Wed, 11 Apr 2007 20:29:47 -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-s12.bay0.hotmail.com (bay0-omc1-s12.bay0.hotmail.com [65.54.246.84]) by postgresql.org (Postfix) with ESMTP id 54B309FB4E4; Wed, 11 Apr 2007 20:29:47 -0300 (ADT) Received: from BAY131-W9 ([65.55.136.44]) by bay0-omc1-s12.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Wed, 11 Apr 2007 16:29:45 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_21f6fa5e-03ff-4ba2-b02e-4d2cf4dcf61e_" 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 16:29:45 -0700 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 11 Apr 2007 23:29:45.0741 (UTC) FILETIME=[4A06CBD0:01C77C91] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/107 X-Sequence-Number: 25171 --_21f6fa5e-03ff-4ba2-b02e-4d2cf4dcf61e_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Joe, =20 The reason why I am asking is, we are building an interface layer where a= ll 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. =20 That's the reason. =20 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: skart= hi98@hotmail.com> CC: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org>= > Hi skarthi,> > On Wed, 2007-04-11 at 15:01 -0700, Karthikeyan Sundaram w= rote:> > 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_nu= mber(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 inser= t to test_vw> > do instead> > insert into test_a (b) values (COALESCE(new.b= 1::bit,'1')||> > COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit)= ;> > > > ERROR: cannot cast type numeric to bit> > > > How will I resolve t= his?> > *My* question is why are you doing such convoluted conversions, fro= m 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 br= oadcast)---------------------------> TIP 6: explain analyze is your friend _________________________________________________________________ Live Search Maps =96 find all the local information you need, right when yo= u need it. http://maps.live.com/?icid=3Dwlmtag2&FORM=3DMGAC01= --_21f6fa5e-03ff-4ba2-b02e-4d2cf4dcf61e_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Joe,

The reason why I am asking is, we are building an interface layer wh= ere all our users will have a view. They shouldn't know anything abou= t how and where the data is stored in the table. They can be seen onl= y 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_st= ring data type
> To: skarthi98@hotmail.com
> CC: pgsql-admin@po= stgresql.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));
> >
> > creat= e 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 fro= m test_a;
> >
> > create or replace rule test_a_ins as o= n 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: ca= nnot 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 t= o me like you want to
> manipulate bits and if that's the case, you s= hould be using the bit
> string operators, as someone pointed out a c= ouple of days ago. In case
> you haven't looked at them, please see:<= BR>>
> http://www.postgresql.org/docs/8.2/static/functions-bitstr= ing.html
>
> Joe
>
>
> -------------------= --------(end of broadcast)---------------------------
> TIP 6: explai= n analyze is your friend

Live Search Maps =96 find all t= he local information you need, right when you need it. Find it! = --_21f6fa5e-03ff-4ba2-b02e-4d2cf4dcf61e_--