Thread: Case (in)sensitivity

Case (in)sensitivity

From
Roberto Mello
Date:
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.

Thanks,

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
"Apple" (c) Copyright 1767, Sir Isaac Newton.


Re: Case (in)sensitivity

From
"Christopher Kings-Lynne"
Date:
> 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.

That's exactly what you have to do, and it works fine.  I can't speak for
the JDBC driver, however I'd hope that it automatically quoted all field and
table names if it could?

Chris



Re: Case (in)sensitivity

From
Peter Eisentraut
Date:
Roberto Mello writes:

> Can someone enlighten me as to what the standard says about case
> sensitivity and how does PostgreSQL stick to it?

http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Case (in)sensitivity

From
Joel Burton
Date:
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