Don't get bit by PostgreSQL case sensitivity

#postgres #database

I’ve ran into PostgreSQL case sensitivity myself before and I’ve had plenty of people ask me about it, so I thought it might be a good thing to bring up here.

Sometimes you hear that PostgreSQL is case-insensitive, but it isn’t really. What it actually does is convert your table and column names to lowercase by default. So take a look at this SQL:

SELECT FullName FROM Person

PostgreSQL actually converts it to this:

SELECT fullname FROM person

That is nice if you happen to like to write your queries with mixed casing.

But you’ll start to run into a problem if you’ve actually created the table and columns using case-sensitive names, which happens when you use quotes around the names. For example, consider these SQL CREATE statements:

CREATE TABLE person (fullname VARCHAR(100), address VARCHAR(100))
CREATE TABLE Person (FullName VARCHAR(100), Address VARCHAR(100))
CREATE TABLE "Person" ("FullName" VARCHAR(100), "Address" VARCHAR(100))

In the first two examples, you get a table called “person“ with two columns called “fullname“ and “address”. That may not be obvious in the second example since the names are not lowercase, but remember that PostgreSQL converts your SQL to lowercase for you.

In the last example, the names are in quotes so their case is maintained. This means you’ll get a table called “Person“ with two columns called “FullName“ and “Address”. Now what happens if you try to run a query with a table called “Person”? Well, using SQL like this:

SELECT FullName FROM Person

you’ll get a syntax error:

ERROR: relation “person does not exist
LINE 1: SELECT FullName FROM Person

This is because PostgreSQL is converting “Person to “person”, but there is no table called “person”. It is actually called “Person”.

To avoid this error you instead have to write the SQL with quotes like this:

SELECT "FullName" FROM "Person"

Obviously that can start to become a bit of a pain, so the moral of the story is don’t use quotes when creating tables or writing SQL queries so that everything is created as lowercase and things will work like you probably expect. You’ll especially want to pay attention to any tools you use to create SQL for you. If you use a tool’s UI to create a table and have a habit of typing in mixed case, the tool might generate SQL (or even the table itself) for you using quotes, which as you can see could mess you up later.

This post originally appeared on the Xojo Blog.

Top comments (6)

Kasey Speakman • Feb 13 '18

I had the same experience and drew the same conclusions. This is by far one of the most irritating things about Postgres when I first started using it.

What Postgres should do is remember exactly what I named things and display them back to me the same way. If I put CREATE TABLE mYfoO (I should be slapped, but...) it should always display the table to me with exactly that capitalization. However, queries should still be case insensitive to follow spec and generally reduce friction. The way it works now is frustrating, because it takes away one of the few tools we have to increase legibility of strung-together words. (My work languages encourage casing instead of underscores, so using underscores in field names is entirely unnatural.) And the alternative of using quotes makes for a frustrating dev experience.

So, I never use quotes. In all my queries I use casing. It has "become normal" to look for lower cased versions of the field names in the admin tools.

Georgi Georgiev • Jan 22 '19

Paul, I'd buy you a drink! You saved me at least 6 hours with this post!

I applied PascalCase convention for the names of my entities in TypeORM, including enums. This resulted in major & mysterious problem during migration generation. After I switched to underscore & lowercase naming it was all done and fixed!

Bio Lab Analytics, LLC • Oct 29 '19

Thank you very much for writing this up! I've been using pgSQL for a long time as a standalone Db (with pgAdmin4) or as a back-end for R/shiny apps, and somehow never ran into this issue. I've been working with Python/Flask and a Flask-SQLAlchemy ORM more recently, and this was almost a showstopper when I went in to validate my commits in pgSQL and saw the cryptic error messages! Your post probably saved me hours of banging my head against the keyboard in frustration :)