PostgreSQL8.3系への移行は悩み多き道

PostgreSQL June 30th, 2008

PostgreSQLの7.4系から一気に8.3系に移行すると大変なことになると知った。

例えばこんなテーブルとデータを用意する。

$ cat ./test_db.sql
CREATE TABLE test_table (
datetime timestamp without time zone NOT NULL DEFAULT '2008-01-01 00:00:00'
);
INSERT INTO test_table (datetime) VALUES ('2008-06-30 01:23:45');

これを7.4系（5432ポートで起動）と8.3系（5433ポートで起動）のPostgreSQLに流し込む。

$ psql -Upostgres -p 5433 -f ./test_db.sql db_test
$ psql -Upostgres -p 5432 -f ./test_db.sql db_test

何が問題かというと、例えば次のようなSQLを発行した場合

$ psql -Upostgres -p 5432 -c "SELECT SUBSTR(datetime, 1, 4) FROM test_table"  db_test
 substr
--------
 2008
(1 row)

7.4系ではSUBSTRのような文字列型データを扱う関数を利用した場合、値が自動的にキャストされていたのだが、

psql -Upostgres -p 5433 -c "SELECT SUBSTR(datetime, 1, 4) FROM test_table"  db_test
ERROR:  function substr(timestamp without time zone, integer, integer) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  SELECT substr(datetime, 1, 4) FROM test_table
ERROR:  function substr(timestamp without time zone, integer, integer) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

8.3系ではパフォーマンスを稼ぐためか、この自動キャストがなくなっている。もちろん

$ psql -Upostgres -p 5433 -c "SELECT SUBSTR(datetime::text, 1, 4) FROM test_table"  db_test
 substr
--------
 2008
(1 row)

明示的にキャストすれば問題ないのだが、移行する全てのアプリケーションのSQLをチェックしなおす必要がある。他にも、日付のカラムをLIKE検索している場合などにも同じようなことになる。

$ psql -Upostgres -p 5433 -c "SELECT *  FROM test_table WHERE datetime LIKE '%0%'"  db_test
ERROR:  operator does not exist: timestamp without time zone ~~ unknown at character 42
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  SELECT *  FROM test_table WHERE datetime LIKE '%0%'
ERROR:  operator does not exist: timestamp without time zone ~~ unknown at character 42
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
$ psql -Upostgres -p 5433 -c "SELECT *  FROM test_table WHERE datetime::text LIKE '%0%'"  db_test
      datetime
---------------------
 2008-06-30 01:23:45
(1 row)

たしかに、型を正しく理解してSQLを書くのが大事なわけだが、今さらこの挙動は困る。

Leave a Reply