Postgres and case sensitivity

[Michael Houlberg]

Bas,

I’ve been reading the docs on this, and also experimenting.  I’ll check out “quoteidentifier”, I didn’t know about that one at all and it sounds like it’s going to cure a lot of my ills!

Thanks!
Michael

On Aug 20, 2014, at 5:37 PM, Bastiaan Olij <bastiaan at basenlily.me> wrote:

> Hi Michael,
> 
> Postgres is case sensitive in its table names and column names but it
> has some interesting behavior to make it semi case insensitive.
> 
> Any query you do where you just write out the names will convert the
> names to lower case.
> 
> So:
> select * from FCUSTOMERS
> actually executes
> select * from fcustomers
> 
> If your table is called anything but fcustomers (fCustomers, FCustomers,
> etc) it will fail, but if the table name is lower case, it doesn't
> matter what case you use in your query.
> 
> If you want to use case you need to put double quotes around the name so:
> 
> select * from "FCUSTOMERS"
> will work, and only work, if the table is called FCUSTOMERS
> 
> The same applies to create/alter table statements. Without the quotes,
> the case is ignored and everything is executed asif it was all
> lowercase, with the quotes the case is retained.
> 
> So there are two strategies you can follow here. One is to always
> include the quotes and thus makes sure everything you do is the right
> case or abandon case all together and never use the quotes.
> 
> The DAM has a property called $quotedidentifier which adds the quotes
> around the table names and column names, make sure it is set to false.
> 
> Hope that helps,
> 
> Bas
> 
> On 21/08/14 10:08 AM, Michael Houlberg wrote:
>> $Listers:
>> 
>> What is with Postgres and case sensitivity and how do you deal with it?
>> 
>> I’m adding a Postgres connection option to my application which already supports SQL Server, MySQL, and OmnisSQL using a lot of common code in my Table Classes and DAM objects.  I’ve already noticed that when I create tables in MySQL, often (but not every time) it converts table names into lower case.  Such as a “fCustomers" becomes “fcustomers”.  Interestingly, column names remain in the form “FirstName” and “LastName”.  But what is great is that executing commands using $select, $insert, $update and the like work anyway.  It’s just like the Omnis SQL Browser where it is case-insensitive when I enter queries.
>> 
>> But Postgres is particular.  I have real problems with $createnames, and creating indexes.  I’m finding it really vexing to come up with a simple solution to making my existing code work.  I don’t want to have to fork every command with “if damname = ‘PGSQLDAM’ all over the place.
>> 
>> Is there some kind of a mode switch on Postgres to make it be case insensitive?
>> 
>> Thanks,
>> Michael Houlberg
>> Houlberg Development, LLC_____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
>> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com