Re: How to avoid nulls while writing string for dynamic query

Dnia 2004-02-13 08:13, Użytkownik Kumar napisał:
> oh, ok understood.
> What will happen for a timestamp field. Let us say c1 is a timestamp column.
> 
> sqlstr := 'insert into test(c1, c2) values
> ('||'\''||COALESCE(rec.c1,'NULL')||'\','
> 
>>>        ||'\''||rec.c2||'\')';
> 
> 
> If this case the query will be
> insert into test(c1,c2) values ('2004-02-13', 'Hai')
> 
> If there is a null value encountered i will return an error for the
> following query
> insert into test(c1,c2) values ('NULL', 'Hai')
> ERROR:  Bad timestamp external representation 'NULL'
It's because you can't use quotes with null. Valid query is:
insert into test(c1,c2) values (NULL, 'Hai');

Your dynamic query will then look like:

sqlstr := 'insert into test(c1, c2) values (' ||COALESCE('\'' || rec.c1 
|| '\'','NULL') ...

or more elegant:

sqlstr := 'insert into test(c1, c2) values (' 
||COALESCE(quote_literal(rec.c1),'NULL') ...

Regards,
Tomasz Myrta