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 blog¹:

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

Read More

• ¹ Teaching Python and data science around the world — Reuven Lerner