Re: Case (in)sensitivity
От | Joel Burton |
---|---|
Тема | Re: Case (in)sensitivity |
Дата | |
Msg-id | Pine.LNX.4.30.0203110223190.27717-100000@temp.joelburton.com обсуждение исходный текст |
Ответ на | Case (in)sensitivity (Roberto Mello <rmello@cc.usu.edu>) |
Список | 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
В списке pgsql-sql по дате отправления: