T

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を書くのが大事なわけだが、今さらこの挙動は困る。

Posted by on 6月 30, 2008 in PostgreSQL

Comments

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

  • コメントを残す