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.

9 thoughts on “Don’t use double quotes in PostgreSQL”

  1. Absolutely agreed. Oracle works similarly – when I first used the Oracle Enterprise Manager GUI tool about 11 years ago, I discovered it would, like PGAdmin, add double quotes as a courtesy for you. I could see this tripping up newbies while providing few benefits. They should teach this as fundamental wisdom in school: junk food is bad, drug abuse is bad, quotes in identifiers are bad.

  2. I beg to differ.

    If you’re skilled in a language you should know what the odd corners are and stay away from them, not just ditch a feature;
    you’re basically relegating identifiers quoting as syntactic sugar, but they’re not there just for the parser.
    EG: properly quoting saves you from writing potentially dangerous regular expressions for search & replace even in case of trailing variations of a given name.

    Besides, most of the DB engineers I work with stick with snake case and we’re all happy with as smart naming schemes get you a long way.

    1. There are definitely cases in which double quotes are useful in PostgreSQL.

      But in the overwhelming majority of cases where I’ve seen double quotes, they have done more harm than good.

      So yes, they’re a great and useful and important feature — for some people, some of the time. For most people, though, they’re asking for trouble. Until and unless you find yourself in a situation where you need them, I think it’s important to warn people away.

  3. Hi,

    Is there any way to command postgres to consider ” as ‘ (Double quote and single quote.)..?

    Thanks in advance
    Shiva

    1. Not as far as I know. I assume that it’s the PostgreSQL parser that is responsible, and you would have to rewrite parts of it (maybe a little, maybe a lot) to get the functionality you’re asking for. And you would have to be really sure that you haven’t broken anything else. So while attractive as an idea, I don’t think it’s realistic.

  4. One case where I had to add double-quotes was where a supplier had provided a sytem with columns called “in” and “out”, and because these are keywords, we had to put quotes around them.

  5. It’s annoying that with Postgres double quotes for identifiers is overloaded to do two things; preserve case AND allow an identifier to be a keyword.

Leave a Reply

Your email address will not be published. Required fields are marked *

ten × one =