PostgreSQL Case Sensitivity Part 1 (The DDL)

Anytime you discuss PostgreSQL, the issue of case sensitivity quickly boils to the top of the conversation. Having used PostgreSQL in several high volume production systems for the last 3 years I will attempt to separate some of the myth and legend out of the conversation and provide some best practices methods that have worked for me.

The first thing to understand, is that there are actually two completely different case sensitivity issues with PostgreSQL. Case sensitivity of the data definition language (DDL) itself, which we will be covering in this first article, and case sensitivity of the data itself, which we will cover in a second article.

Most developers have adapted “Camel-back” as their preferred convention for using case in table and field names. So a table containing customer phone numbers might be “CustomerPhone”. Instead of “customer_phone” or some other variation. And the first name of the customer might be “FirstName” or “CustomerFirstName”. It makes the fields easy to read and there is no need for special characters such as the underscore, which those of us that have evolved from hunt and peck really appreciate!

So what’s the deal? Why is this an issue with PostgreSQL?

PostgreSQL treats all DDL as case sensitive, to assist with this, it forces all SQL code to lowercase before submitting it to the back-end,  If we use camel-back when creating tables and fields in PostgreSQL, via PGAdmin, then the resulting DDL will have double quotes around the fields.  For those of you familiar with MS SQL Server the double quote serves the same purpose as the square brackets ( [  ] ).

CREATE TABLE "CustomerCase"
(
 "CustSysId" serial NOT NULL,
 "FirstName" character varying(20),
 "LastName" character varying(20),
 CONSTRAINT pkcustomercase PRIMARY KEY ("CustSysId" )
)
WITH (
 OIDS=FALSE
);

So why not just get rid of the double quotes? If we do, PostgreSQL will just ignore the case and create the table and fields in all lower case.

What happens if we create the table with double quotes around the fields so we get camel-back field names? Then all of our resulting SQL code must also use double quotes.

Select "FirstName" from "CustomerCase"

Works but any of the following will generate an error

select FirstName from CustomerCase
select FirstName from "CustomerCase"
select firstname from "CustomerCase"

Anything not wrapped with double quotes is being converted to lowercase prior to being submitted to the database. And since the DDL is case sensitive, firstname is not the same field as FirstName. In fact we can demostrate that by creating the following table:

CREATE TABLE "CustomerCase4"
(
 "CustSysId" serial NOT NULL,
 "firstname" character varying(20),
 "FirstName" character varying(20),
 "LastName" character varying(20),
 CONSTRAINT pkcustomercase4 PRIMARY KEY ("CustSysId" )
)
WITH (
 OIDS=FALSE
);

We have two first name columns, as the case is different PostgreSQL treats them as different names. PLEASE PLEASE NEVER DO THIS!!!!

An important fact to note is that the double quotes are not key, the case sensitivity is the key. To select both first name columns above you might think that we need to wrap both with double quotes since they were defined that way, but in reality the double quotes just force the field to be accepted as is instead of converting it to lower case. So both of the below statements will work.

select “FirstName”,firstname from “CustomerCase4”
select “FirstName”,”firstname” from “CustomerCase4”

Notice there are no double quotes around firstname in the first statement. And now for a bonus question …… Will the following statement work and if so what will it do?

select FirstName from "CustomerCase4"

It will not generate a SQL error, remember anything not wrapped in quotes will be converted to lower case, which means the field being selected is actually “firstname” not “FirstName”. The statement that actual gets executed on the server is

select firstname from "CustomerCase4"

If that didn’t make your head hurt just a little bit, than your a better man than me!

So How Do I Deal With Case Sensitivity in the DDL?

By not dealing with it! In otherwords, I long ago quit trying to fight PostgreSQL on the topic and just create all my DDL in all lower case. At first it seemed like the fields were harder to read, but after a little time, I found that my brain sees CustomerFirstName and customerfirstname the same way. Would I prefer to be able to have camel-back in my DDL? …. Of course! But not enough to pay for SQL Server licensing, or be forced to wrap double quotes around every single field, and table name, in every SQL statement I write.

So How is WinDev Development Effected by this?

First any SQL code we do using HExecuteSQLQuery will need to be the same as any code we execute via PGAdmin, meaning that if we had used double quotes and Camel-back in our DDL then our SQL statements would need to also be done using double quotes. So for the same reasons that I stated above, I create my DDL in PostgreSQL as all lower case.

I was hoping that I could change my Analysis to Camel-back and all my standard WX code would be in camel-back and everything would work. However it appears that when the actual code is passed to the back-end everything gets wrapped with double quotes. And the following code

customer.FirstName = "test2"
customer.lastname = "test2"
HAdd(customer)

Will generated the below error.

ERROR: column "FirstName" of relation "customer" does not exist at character 34

After discovering this I had hope it would be considered a bug in the Native PostgreSQL driver that pcSoft could address, but after doing some research on the issue, it appears that every external driver to PostgreSQL has this same issue and it appears to be in the driver itself that the double quotes are being applied.

Summary

So for my development I have adopted the following best practice. I create all my DDL in PostgreSQL in lower case. I import the resulting tables into the Analysis and leave everything as lowercase.

There is one trick that we can do to use the table names as Camel-back. In the table definition we have the ability to name both the table and the external database name of the table. So we can change the name of our table to Camel-back and just leave the external name in lower case.

And then our WX code will look like the following:

Customer.firstname = "test2"
Customer.lastname = "test2"
HAdd(Customer)

The one thing that pcSoft could do to make our life easier is expose an external name for all the items of a table that way we could use the same trick for field names as we can for table names. I have submitted this as a suggestion to pcSoft, feel free to also express your interest in this feature to them!!!

There are of course alternatives to my method. You could use Camel-back in your DDL, and as long as your Analysis has the exact same mixed case all your standard Hxxxx statements in WX would work fine. And if you don’t do a lot of native SQL via HExecuteSQLQuery  or in PGAdmin then that might be an option for you. Just remember that anytime you do use native SQL you will need to double quote all fields and table names.

Another option that many may chose, is to use lower case with an under score (i.e. customer_first_name). Everything will still need to be lowercase but the fields may be easier to read. For me the slightly easier to read field names is not worth having to using the underscore in the field names, but that is a personal preference and there certainly is nothing wrong with any of these options as long as you understand the consequences and it works for you.

Be sure to tune into part two of this article where we will get into case sensitivity of the actual data in PostgreSQL.

[suffusion-the-author display=’author’]

Pete Halsted[suffusion-the-author display=’description’]

 

7 thoughts on “PostgreSQL Case Sensitivity Part 1 (The DDL)

  1. Thanks Pete, a very good and interesting WX using Postgres overview. It seems that using f.i. the native Postgres driver on Linux 64 bit is impossible. More exactly : I would like to deploy a WebService to CentOS 6.x 64 bit using (you guess it) PG 64 bit. Can you confirm that I have to switch to ODBC than ? Thanks

    #pragma

    Like

    1. The issue is actually worst than that, its not a 32 bit vs 64 bit issue. pcSoft has not provided any native PostgreSQL driver for Linux. So to talk to a PostgreSQL database from an WX application running on Linux, you will have to resort to ODBC.

      Be sure not to confuse issue. I don’t believe you have – but I want to make sure other readers understand. The issue is with the OS that you WX application runs on. A WX application such as a Web-service running on a Windows server can use the native driver to talk to a PostgreSQL database running anywhere (Windows, Linux, Solaris, etc.) But if you try to deploy that same Web-service on a Linux server, then you no longer have a native driver for PostgreSQL and have to fall back to ODBC.

      If like me, you need Native PostgreSQL access for Linux, please be sure to let pcSoft know, and hopefully it will move up their priority list!

      Like

  2. Studies on readability have shown that underscore separated words are faster to read and understand than CamelCase. customer_phone is more readable than CustomerPhone.

    Like

    1. Interesting. Did the study also examine the lost productivity of typing the variables with an _ which considerably slows down all but the best touch typist?

      It really is a personal preference. And what ever works for you, can become a Ford vs Chevy talk real quick. For me I guess I have been doing it long enough that I generally can read variable names that are in all lowercase just fine.

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s