Re: Postgres mystery

On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote:
> Can anyone tell me what the problem is here:
> I am inserting into a table via a stored procedure, to a few columns within
> the table and postgres is throwing a
> CANNOT EXECUTE NULL QUERY.
>
> EXECUTE ''INSERT INTO table (column1, column2, column3,
> ''||quote_ident(column4)||'') values
> (''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
> mn2)||'',''''stringvalue'''',''||quote_literal(RECORDNAME.column2)||'')'';

One of the operands to || is probably NULL, so the entire INSERT
string ends up being NULL.  Example:

SELECT 'abc' || 'def';
 ?column?
----------
 abcdef
(1 row)

SELECT 'abc' || NULL;
 ?column?
----------

(1 row)

Looks like you need to check for NULL or use COALESCE to convert
NULL to something else.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/