Skip site navigation (1) Skip section navigation (2)

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

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: Kumar <sgnerd(at)yahoo(dot)com(dot)sg>
Cc: psql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: How to avoid nulls while writing string for dynamic query
Date: 2004-02-13 08:07:15
Message-ID: 402C85B3.2020204@klaster.net (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: KumarDate: 2004-02-13 09:14:24
Subject: Re: How to avoid nulls while writing string for dynamic query
Previous:From: Richard HuxtonDate: 2004-02-13 07:22:17
Subject: Re: 7.4 - FK constraint performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group