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).