Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table whose name has Capital letters

Table whose name has Capital letters

From
Gabriel Fernandez
Date:
Hi ,

I have created a table with the name 'Nov2000' and now it is impossible to
access it, because psql is always searching for the table 'nov2000'.

The only solution I have found is to manually modify the pg_class table and
the name of the physical file associated to the table.

Have I done something wrong or is it a bug ?  I mean, it seems psql is
alwyas converting the query string to lowercase, but it doesn't when you
create a table, a index or a sequence.

I'm running  6.5.3 PostgreSQL on a Linux RedHat 6.0 machine.

Thanks,

Gabi :-)

SV: Table whose name has Capital letters

From
"Jarmo Paavilainen"
Date:
Hi,

...
> I have created a table with the name 'Nov2000' and now it is impossible to
> access it, because psql is always searching for the table 'nov2000'.

Put the table name inside quotes. ie: SELECT FROM "Nov2000" _not_ SELECT
FROM Nov2000

...
> Have I done something wrong or is it a bug ?  I mean, it seems psql is
> alwyas converting the query string to lowercase, but it doesn't when you
> create a table, a index or a sequence.

*I think* its a bug. But no it is not, its so by design (SQL92 thing).

// Jarmo

PS: Im thinking of making a own hack of pg where selects are case sensitive
(ie Nov2000 and "Nov2000" are the same). If someone is interested, Ill
upload the diff somewhere. DS.

Re: SV: Table whose name has Capital letters

From
Peter Eisentraut
Date:
Jarmo Paavilainen writes:

> PS: Im thinking of making a own hack of pg where selects are case sensitive
> (ie Nov2000 and "Nov2000" are the same). If someone is interested, Ill
> upload the diff somewhere. DS.

Not that I particularly approve of this plan ;-) but look for tolower()
calls in src/backend/parser/scan.l.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Re: Table whose name has Capital letters

From
Jeff Davis
Date:
I have not used that version of postgres for a while, but on my version
(7.0.2) that scenario works fine. The way it is supposed to work, everything
should be case-insensitive. It should not matter whether you ask for nov2000
or Nov2000, it should be the same table, and should give you an error if you
try to make both tables.

You may have encountered a bug, but it is unlikely to be fixed in the
6.* series, since the primary focus is on the 7.* series right now. It is
fixed (I think) in 7.0.2, so I suggest upgrading if it is convenient.

Hope this helps,
Jeff Davis

Gabriel Fernandez wrote:

> Hi ,
>
> I have created a table with the name 'Nov2000' and now it is impossible to
> access it, because psql is always searching for the table 'nov2000'.
>
> The only solution I have found is to manually modify the pg_class table and
> the name of the physical file associated to the table.
>
> Have I done something wrong or is it a bug ?  I mean, it seems psql is
> alwyas converting the query string to lowercase, but it doesn't when you
> create a table, a index or a sequence.
>
> I'm running  6.5.3 PostgreSQL on a Linux RedHat 6.0 machine.
>
> Thanks,
>
> Gabi :-)

Re: Table whose name has Capital letters

From
Tom Lane
Date:
Jeff Davis <jdavis@wasabimg.com> writes:
> The way it is supposed to work, everything should be
> case-insensitive. It should not matter whether you ask for nov2000 or
> Nov2000, it should be the same table, and should give you an error if
> you try to make both tables.

I am betting that he initially did CREATE TABLE "Nov2000", and is now
trying to access the table without using double quotes around its name.
Which won't work...

            regards, tom lane