Wednesday, February 03, 2010

DOH - conversions again

I was just running a series of long-running statements...An insert statement bombed because I had hard-coded a null value to insert into a newly added column. (As our data providers feed us the data later, it will be populated.) Use of a subquery seemed to confuse Postgres, breaking the statement. A simple example can illustrate:

core=# create table delete_me (a numeric);

/* This works */
core=# insert into delete_me select null;

/* This does NOT work */
core=# insert into delete_me select a from (select null as a) sub;
ERROR: failed to find conversion function from unknown to numeric