Thread: 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