Обсуждение: 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