On 03/18/2013 03:25 AM, Charl Roux wrote:
> Thanks. Case-folding was my problem.
> Is there any way of getting PostgreSQL to work according to the SQL
> standard (The folding of unquoted names to lower case in PostgreSQL is
> incompatible with the SQL standard, which says that unquoted names
> should be folded to upper case.), so there is no need for me to add
> quotes to all names?
>
As far I know the only difference would be whether the table name you
get in the error comes back as lower case or upper case. In your
situation where you are using a quoted mixed case both folding styles
would lead to an error. In that aspect the SQL standard and Postgres
follow the same rule, if the name is quoted on creation the case it was
quoted in must be maintained on subsequent use. If you do not want to
quote identifiers on use then do not quote then on creation:
test=> create TABLE case_test("MixedCaseQuoted" varchar,
MixedCaseUnQuoted varchar);
CREATE TABLE
test=> \d+ case_test
Table "utility.case_test"
Column | Type | Modifiers | Storage |
Description
-------------------+-------------------+-----------+----------+-------------
MixedCaseQuoted | character varying | | extended |
mixedcaseunquoted | character varying | | extended |
Has OIDs: no
test=> SELECT mixedcasequoted from case_test ;
ERROR: column "mixedcasequoted" does not exist
LINE 1: SELECT mixedcasequoted from case_test ;
^
test=> SELECT mixedcaseunquoted from case_test ;
mixedcaseunquoted
-------------------
(0 rows)
test=> SELECT "MixedCaseQuoted" from case_test ;
MixedCaseQuoted
-----------------
(0 rows)
test=> SELECT MixedCaseUnQuoted from case_test ;
mixedcaseunquoted
-------------------
(0 rows)
--
Adrian Klaver
adrian.klaver@gmail.com
