Re: Case (in)sensitivity - Mailing list pgsql-sql

On Sun, 10 Mar 2002, Roberto Mello wrote:

> Can someone enlighten me as to what the standard says about case
> sensitivity and how does PostgreSQL stick to it?
>
> I always name my tables and columns, everything in lower case. While
> helping somebody with migration from MySQL to PostgreSQL, I found out
> that PostgreSQL is case sensitive and that psql converts everything to
> lower case.
>
> I also found out that pgaccess allows you to create a table like "tEsT"
> but then you can't do 'SELECT * FROM tEsT'. It simply doesn't find the
> table. I haven't tried with 'SELECT * FROM "tEsT"' yet but I think it
> should work.
>
> Same oddity happens with the JDBC driver apparently.
>
> I thought the SQL standard dictated non-case sensitive, but it doesn't
> appear to be so. If the standard dictates case-sensitivity, why does psql
> convert everything to lowercase? Is there any way to configure it to
> respect case sensitivity then? How about the JDBC driver?
>
> Any information in that regard would be great.

This is a FAQ.

PG is *not effectively* case sensitive, normally. If you create a table
called Test, you can search for it as test or TEST. PostgreSQL simply
converts all of these to lower case, so you'll always find it.

However, if you use the system identifier quotes (") around a
table/field/whatever name, PG will keep your case. So if you create that
table as "Test", you'll only find it as "Test".

I think this is the best of both worlds. You can feed PostgreSQL a MySQL
create-script which uses case-sensitive table names (MySQL is
case-sensitive for table names on many platforms), and PG will do the
right thing. You can feed it a create-script that is case-insensitive
about column names and it will do the right thing. It's only if you put
the system identifiers around it that it keeps things in exact case.

My practice is to say things like this:

CREATE TABLE Test (id int, foo varchar(5));

and

SELECT id, foo FROM Test;

(keeping columns as lowercase and tables as initcapped.) However, since
these aren't quoted, PG really records these as "test", "id", "foo"). So,
if you were lazy or working with a report writer that tried to change case
on you, you could also SELECT * FROM TEST, SELECT * FROM test, or
(perversely) SELECT * FROM tEsT and it would work fine.

You can't, however, SELECT * FROM "test" or SELECT * FROM "TEST" because
that would look for the exact capitalization "test" or "TEST", which I
haven't created. This is what some clients might do, and this is where
your problem might be.

HTH.
-- 

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant