r_nobuホームページ (のぶねこブログ)

タイムスタンプ型のカラムをlikeでパターン検索する時にPostgreSQL 7.4とか8.1とか8.2では適当なSQL文にしていても動作していたのだが、8.3だとSQL文を厳密にしないと動作しなくなった。
例えば、以下のようなSQLを実行したとする。
select * from nobuneko_table where nobuneko_time like '%-%-%';
※この例では、「nobuneko_time」がタイムスタンプ型のカラムで、このカラムには例えば「2010-05-02」といったような値が入る。

8.3よりも前のバージョンだとこの書き方でも結果が返っていたのだが、8.3だと以下のようなエラーになってしまう。
operator does not exist: timestamp without time zone ~~ unknown

likeは文字列検索なのにタイムスタンプ型のカラム「nobuneko_time」で検索しようとしているため、型の不一致で上記のようなエラーが発生している。8.3より前のバージョンだとPostgreSQL側が自動的に型を変換（キャスト）してくれていたのだが、8.3では自動的にキャストしてくれなくなったようだ。従って、PostgreSQL 8.3では、自分で明示的にキャストするようなSQL文を書く必要がある。

まとめると、以下のようになる。

＜PostgreSQL 7.4とか8.1とか8.2＞
select * from nobuneko_table where nobuneko_time like '%-%-%';
タイムスタンプ型の「nobuneko_time」がテキスト型に自動的にキャストされるため、このSQLでも結果が返る。

＜PostgreSQL 8.3＞
select * from nobuneko_table where nobuneko_time like '%-%-%';
タイムスタンプ型の「nobuneko_time」がテキスト型に自動的にキャストされないため、このSQLでは以下のようなエラーとなる。
operator does not exist: timestamp without time zone ~~ unknown
※8.3よりも前のバージョンのように自動的にキャストされないのであれば、SQL文のタイムスタンプ型のカラムの直後に手動で「::text」を追記して明示的にキャストすればよい。

※今回の例では、以下のようにすればエラーが出なくなる。
select * from nobuneko_table where nobuneko_time::text like '%-%-%';

また、上記と同様にto_timestampを使用する時もキャストする必要がある。
例えば、8.3よりも前のバージョンだと問題なかったのだが、
to_timestamp(current_date, 'YYYY/MM')
とすると、8.3では
function to_timestamp(date, unknown) does not exist
とか
operator does not exist: text >= date
といったエラーになる。
そこで、
to_timestamp(current_date::text, 'YYYY/MM')
といったように明示的にテキスト型にキャストする必要がある。

以上。