pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase (in)sensitivity
4+ messages / 4 participants
[nested] [flat]
* Case (in)sensitivity
@ 2002-03-11 05:02 Roberto Mello <rmello@cc.usu.edu>
2002-03-11 05:04 ` Re: Case (in)sensitivity Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-03-11 05:18 ` Re: Case (in)sensitivity Peter Eisentraut <peter_e@gmx.net>
2002-03-11 07:28 ` Re: Case (in)sensitivity Joel Burton <joel@joelburton.com>
0 siblings, 3 replies; 4+ messages in thread
From: Roberto Mello @ 2002-03-11 05:02 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Case (in)sensitivity
2002-03-11 05:02 Case (in)sensitivity Roberto Mello <rmello@cc.usu.edu>
@ 2002-03-11 05:04 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-03-11 05:04 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Case (in)sensitivity
2002-03-11 05:02 Case (in)sensitivity Roberto Mello <rmello@cc.usu.edu>
@ 2002-03-11 05:18 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2002-03-11 05:18 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Case (in)sensitivity
2002-03-11 05:02 Case (in)sensitivity Roberto Mello <rmello@cc.usu.edu>
@ 2002-03-11 07:28 ` Joel Burton <joel@joelburton.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-03-11 07:28 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-03-11 07:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-11 05:02 Case (in)sensitivity Roberto Mello <rmello@cc.usu.edu>
2002-03-11 05:04 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-03-11 05:18 ` Peter Eisentraut <peter_e@gmx.net>
2002-03-11 07:28 ` Joel Burton <joel@joelburton.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox