PostgreSQL – Blocking UPDATE on New Column

postgresqlupdate

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

Best Answer

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.

Related Solutions

PostgreSQL Update – How to Update Different Columns on Different Clauses

I would probably do something like this:

UPDATE tablename
SET col1 = col1 + CASE WHEN col1 > $VAR THEN 2 ELSE 0 END,
    col2 = col2 + CASE WHEN col2 >= $VAR THEN 2 ELSE 0 END
WHERE col1 > $VAR OR col2 >= $VAR

Make sure that col1 and col2 are indexed if you are only updating a few rows and tablename has many rows.

Postgresql – Trying to set up new data_directory on Postgres 9.3

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
Create every directory up to the location you want for the new datadir.
Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
stop PostgreSQL
mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.

Best Answer

Related Solutions

PostgreSQL Update – How to Update Different Columns on Different Clauses

Postgresql – Trying to set up new data_directory on Postgres 9.3

Related Question