On PostgreSQL Quoted Column Identifiers

I was having trouble when doing an update on a temporary table in a PostgreSQL Stored Procedure that turned out to be a problem with how I was identifying Column Names. Before we get to our quick lesson on Column Identifiers, here’s an excerpt from the PostgreSQL docs.

The delimited identifier or quoted identifier is formed by enclosing an arbitrary sequence of characters in double-quotes (“). A delimited identifier is always an identifier, never a key word. So “select” could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:

UPDATE “my_table” SET “a” = 5;

Here’s what we want to avoid: COLUMN DOES NOT EXIST!

Let’s look at a block of plpgsql code where we create a temporary table, populate it and use it to update another temporary table. Notice the double-quotes when defining the table columns. Now look at the UPDATE statement at bottom and see how we have to use the double-quotes when referencing the columns of the (ironically named) columncomments temp table.

Let’s look at another approach. No quotes around the column names when defining the temporary table, and thus no quotes in the UPDATE statement.

So next time you’re banging your head on your desk after PostgreSQL tells you a column doesn’t exist, think Column Identifiers, or more specifically, “Quotation Marks!”