June 30, 2019
Imagine the following situation - you’re trying to select columns from a Postgres table
create table if not exists media ( "MediaKey" text primary key , "ChangedByID" text , "MediaCategory" text , "MediaHTML" text ... );
Let’s assume these are the only four fields you want to select and you want to do it where the
MediaCategory is equal to Audio.
You might expect the following query to work:
SELECT MediaKey, ChangedByID, MediaCategory, MediaHTML FROM media WHERE MediaCategory = 'Audio';
In SQL, you’d be right. If you’re using Postgres, you’d be wrong. Instead, you get a syntax error.
That’s because while SQL is not case-sensitive, Postgres is. Even more confusingly, the engine will automatically convert your strings to lower case, unless instructed not too.
That’s where quotes can come in handy.
We already used single quotes to ensure that we match on the text literal, but trying that with
’MediaQuery’ is likely not what you’re looking for. Instead, we need a Double Quotes.
SELECT "MediaKey", "ChangedByID", "MediaCategory", "MediaHTML" FROM media WHERE "MediaCategory" = 'Audio';
The best summary of the difference I found comes from Reuven Lerner’s blog1:
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. — Reuven Lerner
Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!