Don’t use double quotes in PostgreSQL

I spend a large proportion of my time teaching classes in a variety of open-source technologies — specifically, Ruby, Python, PostgreSQL, and Git.  One of the questions that invariably arises in these classes has to do with the case sensitivity of the technology in question. That is, is the variable “x” the same as the variable “X”?

In nearly ever case, the technologies with which I work are case sensitive, meaning that “x” and “X” are considered two completely different identifiers.  Indeed, the Ruby language goes so far as to give capitalized identifiers a special status, calling them “constants.”  (They’re not really constants, in that you can always redefine a Ruby constant.  However, you will get a warning when you reassign it.  For this reason, I prefer to call them “stubborns,” so that people don’t get the wrong idea.)

SQL is a completely different story, however: The SQL standard states that SQL queries and identifiers (e.g., table names) aren’t case sensitive.  Thus, there’s no difference between

select id, email from people;

and

SELECT ID, EMAIL FROM PEOPLE;

I find both of these styles to be somewhat unreadable, and over the years have generally followed Joe Celko‘s advice for capitalization in SQL queries:

1. SQL keywords are in ALL CAPS,
2. Table names have Initial Caps, and
3. Column names are all in lowercase.

Given that rule, the above query would look like this:

SELECT id, email FROM People;

Again, this capitalization scheme is completely ignored by PostgreSQL.  It’s all for our benefit, as developers, who want to be able to read our code down the road.

Actually, that’s not entirely true: PostgreSQL doesn’t exactly ignore the case, but rather forces all of these names to be lowercase. So if you say

CREATE TABLE People (
  id SERIAL NOT NULL,
  email TEXT NOT NULL,
  PRIMARY KEY(id)
);

PostgreSQL will create a table named “people”, all in lowercase. But because of the way PostgreSQL works, forcing all names to lowercase, I can still say:

SELECT * FROM People;

And it will work just fine.

Now, there is a way around this, namely by using double quotes.  Whereas single quotes in PostgreSQL are used to create a text string, double quotes are used to name an identifier without changing its case.

Let me say that again, because so many people get this wrong: Single quotes and double quotes in PostgreSQL have completely different jobs, and return completely different data types.  Single quotes return text strings.  Double quotes return (if you can really think of them as “returning” anything) identifiers, but with the case preserved.

Thus, if I were to repeat the above table-creation query, but use double quotes:

CREATE TABLE "People" (
 id SERIAL NOT NULL,
 email TEXT NOT NULL,
 PRIMARY KEY(id)
);

I have now created a table in which the table name has not been forced to lowercase, but which has preserved the capital P.  This means that the following query will now fail:

select * from people;

ERROR: relation "people" does not exist
LINE 1: select * from people;
                      ^

It fails because I have created a table “People”, but I have told PostgreSQL to look for a table “people”.  Confusing?  Absolutely.  If you use double quotes on the name of a table, column, index, or other object when you create it, and if there is even one capital letter in that identifier, you will need to use double quotes every single time you use it.  That’s frustrating for everyone involved — it means that we can’t use the nice capitalization rules that I mentioned earlier, and that various queries will suddenly fail to work.

The bottom line, then, is to avoid using double quotes when creating anything.  Actually, you should avoid double quotes when retrieving things as well — otherwise, you might discover that you’re trying to retrieve a column that PostgreSQL doesn’t believe exists.

Now, let’s say that you like this advice, and you try to take it to heart.  Unfortunately, there are places where you still might get bitten, despite your best efforts.

For example, the GUI tool for PostgreSQL administration, PGAdmin 3, is used by many people.  (I’m an old-school Unix guy, and thus prefer the textual “psql” client.)  I’ve discovered over the years that while PGAdmin might be a useful and friendly way to manage your databases, it also automatically uses double quotes when creating tables.  This means that if you create a table with PGAdmin, you might find yourself struggling to find or query it afterwards.

Another source of frustration is the Active Record ORM (object-relational mapper), most commonly used in Ruby on Rails. Perhaps because Active Record was developed by users of MySQL, whose table and column names are case-sensitive by default, Active Record automatically puts double quotes around all table and column names in queries. This can lead to frustrating incompatibilities — such as if you want to access the column in Ruby using CamelCase, but in a case-insensitive way in the database.

PostgreSQL is a fabulous database, and has all sorts of great capabilities.  Unless you really want your identifiers to be case-sensitive, though, I strongly suggest that you avoid using double quotes.  And if you encounter problems working with columns, check the database logs to see whether the queries are being sent using double quotes.  You might be surprised, and manage to save yourself quite a bit of debugging time.