Re: Error with ALTER TABLE %s

On Tue, May 04, 2010 at 10:08:36AM -0700, Tim Roberts wrote:
> Federico Di Gregorio wrote:
> > On 04/05/2010 13:54, [email protected] wrote:
> >   
> >> Hi, when I do
> >> SQL = "ALTER TABLE films ADD COLUMN (%s) text;"
> >> data = ("test3", )
> >> cur.execute(SQL,data)
> >>
> >> I get this error,
> >>
> >> LINE 1: ALTER TABLE films ADD COLUMN (E'test3') text;
> >>     
> >
> > You can't use bound variables with column (or table, or functions, etc.)
> > names.
> >   
> 
> Right, so for posterity's sake, the OP should just use this:
>    cur.execute(SQL % data)

And one last bit for the archives:
If you want/need to use a reserved keyword or preserve MixedCase for a
identifier, it can be quoted, with double-quotes:

>>> data=('"MixedCase"',)
>>> SQL % data
'ALTER TABLE films ADD COLUMN ("MixedCase") text;'

Not that I'd recommend that. Had an interface do that to me many years
ago. Made building manual queries a painful process:

SELECT "MyColumn" from "MyTable" join "MyOtherTable" where
"MyTable"."MyId" == "MyOtherTable"."ParentId" ...

**shudder**

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm-OhmvVRJSr/[email protected]
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
The Connexions Project      http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE


> 
> -- 
> Tim Roberts, [email protected]
> Providenza & Boekelheide, Inc.
> 
> _______________________________________________
> Psycopg mailing list
> Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
> http://lists.initd.org/mailman/listinfo/psycopg
>