PostgreSQL で指数表記の暗黙的キャストが意図しない型になることがある

指数表記は大きな数や小さな数を表記するのに便利です。

（例）
1,000 = 1e+3
1,000,000 = 1e+6
1,000,000,000 = 1e+9

PostgreSQL でも指数表記を解釈してくれます。ですが、指数表記の暗黙的キャストが意図した型になるとは限らないことに注意が必要です。

PostgreSQL 14.2 で検証しました。また、PostgreSQL 10.20 でも同様の結果となることを確認しました。

=# CREATE TABLE tab1 (id integer PRIMARY KEY);

=# INSERT INTO tab1 SELECT generate_series(1, 1e+3);

=# VACUUM ANALYZE;

=# EXPLAIN SELECT * FROM tab1 WHERE id = 1000;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.29 rows=1 width=4)
   Index Cond: (id = 1000)
(2 rows)

=# EXPLAIN SELECT * FROM tab1 WHERE id = 1e+3;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on tab1  (cost=0.00..20.00 rows=5 width=4)
   Filter: ((id)::numeric = '1000'::numeric)
(2 rows)

"id = 1000" でも "id = 1e+3" でも得られる結果は同じです。ですが、異なる実行計画が生成されています。id 列が integer 型なのですから "1e+3" も integer 型にキャストしてほしいのですが、実際は numeric 型にキャストされています。id 列も numeric 型にキャストされていますし、インデックスを利用できておらず、非効率な実行計画を選択していることが分かります。

"1e+3" を integer 型で解釈させるためには明示的にキャストを行います。

=# EXPLAIN SELECT * FROM tab1 WHERE id = 1e+3::integer;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.29 rows=1 width=4)
   Index Cond: (id = 1000)
(2 rows)

実行計画もインデックスを利用しており、改善されたことが分かります。