Postgres blocking UPDATE on new column?

Question

For the life of me, I can't figure out why Postgres won't let me issue this update statement below. Assume any table called dim_dates where there's a column dateType:

ALTER TABLE dim_dates DROP COLUMN "dateType";
ALTER TABLE dim_dates ADD COLUMN "dateType" character(1);
update dim_dates set dateType='D' where year>2000;

It barfs on line 3, saying:

ERROR: column "datetype" of relation "dim_dates" does not exist
SQL state: 42703
Character: 126

Yet I just (re)added the column! I initially had already added the column through the designer, and was attempting to fill in the data for the new table and was only running what is now line 3, but it just doesn't want to let me update the value. I don't want 'D' as a default - but the existing data should have 'D' and I'm going to add further data.

This is a new database, 9.3, no triggers or other evil things to mess up developers (I'm the guy who installed it, and I'm old hat at MS-SQL and MySql, but new to Postgres and trying to get experience with it).

Answer 1

It's a case-sensitivity issue.

"dateType" including the quotes is a quoted identifier, which implies that it retains its case (as opposed to an unquoted identifier which is implicitly converted to lower case). Quoted identifiers allows other columns of the same table named for instance "DateType" or "datetype" or other variants that differ only by case (not that it would be a good idea).

The drawback is that every subsequent SQL instruction refering to that column must use "dateType" too, with the double quotes and the exact same case. In your UPDATE, the double quotes are lacking, which is why it errors out saying that "datetype" does not exist (note how it differs from "dateType").

In practice, an identifier should be either always double-quoted, or never. A mix and match is not allowed. When an identifier is not quoted, it can be written in camel case if you fancy that style: the SQL parser will ignore the case.

See Identifiers and keywords in PostgreSQL documentation for more.