Thread: problem of upper/lower case in table names
Hi all, i've got this annoying problem : if you create a table with an uppercase name, postgres transforms it in lower case. After that, if you try to retrieve the primary keys for this table ( still using the uppercase name as argument ) using the JDBC driver DatabaseMetaData.getPrimaryKeys(), it always return an empty result set since the SQL command used in the driver requests a case sensitive match for the table name ( and bc.relname ~ table ). I've a workaround in the driver ( using bc.relname ~* table ) but i'd like to know who's fault it is ( mine or postgres ) .... Thanks for your advices & help NB : i'm using postgres 6.4 with jdbc driver included in this release.
>Hi all, > >i've got this annoying problem : if you create a table with an uppercase >name, postgres transforms it in lower case. After that, if you try to >retrieve the primary keys for this table ( still using the uppercase >name as argument ) using the JDBC driver >DatabaseMetaData.getPrimaryKeys(), it always return an empty result set >since the SQL command used in the driver requests a case sensitive match >for the table name ( and bc.relname ~ table ). >I've a workaround in the driver ( using bc.relname ~* table ) but i'd >like to know who's fault it is ( mine or postgres ) .... >Thanks for your advices & help I've run into the same problem just creating a table with a name including an uppercase. The name gets converted to all lowercase but then I seem unable to DROP the table. >NB : i'm using postgres 6.4 with jdbc driver included in this release. -- Jay O'Connor joconnor@roadrunner.com http://www.roadrunner.com/~joconnor "God himself plays the bass strings first when He tunes the soul"
> >i've got this annoying problem : if you create a table with an uppercase > >name, postgres transforms it in lower case. After that, if you try to > >retrieve the primary keys for this table ( still using the uppercase > >name as argument ) using the JDBC driver > >DatabaseMetaData.getPrimaryKeys(), it always return an empty result set > >since the SQL command used in the driver requests a case sensitive match > >for the table name ( and bc.relname ~ table ). > >I've a workaround in the driver ( using bc.relname ~* table ) but i'd > >like to know who's fault it is ( mine or postgres ) .... > >Thanks for your advices & help Try quoting the names... i.e. CREATE TABLE "TEST" etc. Taral
On Tue, 17 Nov 1998, Charles-Edouard Ruault wrote:
> Hi all,
>
> i've got this annoying problem : if you create a table with an uppercase
> name, postgres transforms it in lower case. After that, if you try to
> retrieve the primary keys for this table ( still using the uppercase
> name as argument ) using the JDBC driver
> DatabaseMetaData.getPrimaryKeys(), it always return an empty result set
> since the SQL command used in the driver requests a case sensitive match
> for the table name ( and bc.relname ~ table ).
> I've a workaround in the driver ( using bc.relname ~* table ) but i'd
> like to know who's fault it is ( mine or postgres ) ....
> Thanks for your advices & help
It looks like the drivers. I'll check both this and the primary key
problem today, and will get back to you.
>
> NB : i'm using postgres 6.4 with jdbc driver included in this release.
>
>
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
On Tue, 17 Nov 1998, Taral wrote: > > >i've got this annoying problem : if you create a table with an uppercase > > >name, postgres transforms it in lower case. After that, if you try to > > >retrieve the primary keys for this table ( still using the uppercase > > >name as argument ) using the JDBC driver > > >DatabaseMetaData.getPrimaryKeys(), it always return an empty result set > > >since the SQL command used in the driver requests a case sensitive match > > >for the table name ( and bc.relname ~ table ). > > >I've a workaround in the driver ( using bc.relname ~* table ) but i'd > > >like to know who's fault it is ( mine or postgres ) .... > > >Thanks for your advices & help > > Try quoting the names... i.e. CREATE TABLE "TEST" etc. > Very important note: quoting names of tables, indicies, fields etc. SHOULD solve this problem, but be careful! As far as I know there are some bugs related to usage of capital letters in tables and indicies names, so wait for officail patch that should be published during next few days with 6.4.1 release (or try to found one on hackers' list archive). Aleksey