prepare statements 와 casting

타 DB에서 운영하던 prepare statement를 postgreSQL로 포팅이후 못보던 오류(주로 서로다른 타입에 대한 operator가 없다는 등의)를 경험하게 되는데요. 이는 postgresql에서 parepare 구문을 이용하여 바인드변수로 SQL을 실행할때 묵시적형변환을 지원하지 않기 때문입니다.

먼저 일반적인 경우라면 아래와 같이 자동형변환을 확인할 수 있지만...

test=# create table test_table(col1 numeric);
CREATE TABLE
test=# insert into test_table values(1);
INSERT 0 1
test=# explain select col1 from test_table where col1 = '1';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------
 Seq Scan on test_table  (cost=10000000000.00..10000000026.38 rows=7 width=32)
   Filter: (col1 = 1::numeric)
(2 rows)

바인딩변수를 이용하여 실행해 보면 numeric 타입과 varchar 타입간의 = operator를 찾을 수없다는 오류가 발생합니다.

test=# prepare foo(varchar) as
test-# select col1 from test_table where col1 = $1;
ERROR:  operator does not exist: numeric = character varying
LINE 2: select col1 from test_table where col1 = $1;
                                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
test=# 

이는 형변환을 명시함으로써 해결할 수 있습니다.

test=# prepare foo(varchar) as
select col1 from test_table where col1 = $1::numeric;
PREPARE
test=# execute foo('1');
 col1 
------
    1
(1 row)