PostgreSQL8.3系への移行は悩み多き道
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を書くのが大事なわけだが、今さらこの挙動は困る。
Comments
[…] 以前のエントリでPostgreSQL8.3への移行は型キャストが自動的に行われないため結構大変だと書いたが、PostgreSQL8.3にはCREATE CASTという新しいキャストの定義を記述できる機能がある。Let’s Postgresの記事で知った。風説の流布もいいところだった。以下の内容はLet’s Postgresの記事にtimestampを追加しただけ。まったくもって無知の涙である。 […]