Обсуждение: Bit string help, please

Hey PostgreSQL Gurus,

I am experimenting with PostgreSQL bit strings to see if they might help
with some performance issues I am having.  I added a "bit varying"
column to one of my tables.  I have a PL/pgSQL function with an insert
statement into this table.  For the bit varying column I would like to
insert a bit string of length n with the highest order bit being 1 and
all the other bits being 0.  Given a table definition:

create table table1 (  a int,  b bit varying
);

I hoped I could use the feature where casting right pads 0s to do
something like this:

insert into table1 values( DEFAULT, B'1'::bit( n ) );

Where n is one of the parameters to the PL/pgSQL function, but that
doesn't work.  PostgreSQL doesn't like having a variable for the bit
string length.  Does anyone have any ideas how I could achieve this?

Thanks,
Ed Tyrrill

tyrrill_ed@emc.com wrote:
> 
> insert into table1 values( DEFAULT, B'1'::bit( n ) );
> 
> Where n is one of the parameters to the PL/pgSQL function, but that
> doesn't work.  PostgreSQL doesn't like having a variable for the bit
> string length.  Does anyone have any ideas how I could achieve this?

Try casting from a string:  SELECT ('1' || repeat('0', n-1))::bit varying;


--   Richard Huxton  Archonet Ltd

I tried this real quick at the psql command prompt, and unfortunately it
doesn't work:

mydb=# select ('1' || repeat('0',7))::bit varying;
ERROR:  cannot cast type text to bit varying

I appreciate the try though.  Any other ideas?  I am using PostgreSQL
8.2.3.1.  I don't know if that matters too much.

Thanks,
Ed

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 06, 2008 12:14 AM
To: Tyrrill, Ed
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please

tyrrill_ed@emc.com wrote:
>
> insert into table1 values( DEFAULT, B'1'::bit( n ) );
>
> Where n is one of the parameters to the PL/pgSQL function, but that
> doesn't work.  PostgreSQL doesn't like having a variable for the bit
> string length.  Does anyone have any ideas how I could achieve this?

Try casting from a string:  SELECT ('1' || repeat('0', n-1))::bit varying;


--   Richard Huxton  Archonet Ltd

tyrrill_ed@emc.com wrote:
> I tried this real quick at the psql command prompt, and unfortunately it
> doesn't work:
> 
> mydb=# select ('1' || repeat('0',7))::bit varying;
> ERROR:  cannot cast type text to bit varying
> 
> I appreciate the try though.  Any other ideas?  I am using PostgreSQL
> 8.2.3.1.  I don't know if that matters too much.

Ah, sorry - that was an 8.3-only cast.

I think your only real option will be to build your query as a string 
and use EXECUTE to get the bit-varying you want in your function.

--   Richard Huxton  Archonet Ltd

Thanks for the help again, Richard.  I will look into upgrading to 8.3
as I think that will be the best solution.

Ed Tyrrill

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 06, 2008 10:36 AM
To: Tyrrill, Ed
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please

tyrrill_ed@emc.com wrote:
> I tried this real quick at the psql command prompt, and unfortunately
it
> doesn't work:
>
> mydb=# select ('1' || repeat('0',7))::bit varying;
> ERROR:  cannot cast type text to bit varying
>
> I appreciate the try though.  Any other ideas?  I am using PostgreSQL
> 8.2.3.1.  I don't know if that matters too much.

Ah, sorry - that was an 8.3-only cast.

I think your only real option will be to build your query as a string
and use EXECUTE to get the bit-varying you want in your function.

--   Richard Huxton  Archonet Ltd

<p><font size="2">Hi Ed,<br /><br /> I tried exactly what you did and it works for me.<br /> My postgres is 8.3.0
runningon redhat advanced<br /> server.  This is what it told me:<br /><br /> prompt=# SELECT ('1' || repeat('0',
7))::bitvarying;<br />   varbit <br /> ----------<br />  10000000<br /> (1 row)<br /><br /> prompt=#<br /><br />
Regards,<br/><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /><br /> -----Original Message-----<br />
From:pgsql-sql-owner@postgresql.org on behalf of tyrrill_ed@emc.com<br /> Sent: Thu 3/6/2008 10:24 AM<br /> To:
dev@archonet.com<br/> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] Bit string help, please<br /><br /> I tried
thisreal quick at the psql command prompt, and unfortunately it<br /> doesn't work:<br /><br /> mydb=# select ('1' ||
repeat('0',7))::bitvarying;<br /> ERROR:  cannot cast type text to bit varying<br /><br /> I appreciate the try
though. Any other ideas?  I am using PostgreSQL<br /> 8.2.3.1.  I don't know if that matters too much.<br /><br />
Thanks,<br/> Ed<br /><br /><br /> -----Original Message-----<br /> From: Richard Huxton [<a
href="mailto:dev@archonet.com">mailto:dev@archonet.com</a>]<br/> Sent: Thursday, March 06, 2008 12:14 AM<br /> To:
Tyrrill,Ed<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] Bit string help, please<br /><br />
tyrrill_ed@emc.comwrote:<br /> ><br /> > insert into table1 values( DEFAULT, B'1'::bit( n ) );<br /> ><br />
>Where n is one of the parameters to the PL/pgSQL function, but that<br /> > doesn't work.  PostgreSQL doesn't
likehaving a variable for the bit<br /> > string length.  Does anyone have any ideas how I could achieve this?<br
/><br/> Try casting from a string:<br />    SELECT ('1' || repeat('0', n-1))::bit varying;<br /><br /><br /> --<br />
  Richard Huxton<br />    Archonet Ltd<br /><br /><br /> --<br /> Sent via pgsql-sql mailing list
(pgsql-sql@postgresql.org)<br/> To make changes to your subscription:<br /><a
href="http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql">http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql</a><br
/><br/></font> 

tyrrill_ed@emc.com wrote:
> Thanks for the help again, Richard.  I will look into upgrading to 8.3
> as I think that will be the best solution.

I don't think you'll regret it. There are some excellent improvements in 8.3

--   Richard Huxton  Archonet Ltd