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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kumar | 2004-02-13 09:14:24 | Re: How to avoid nulls while writing string for dynamic query |
Previous Message | Richard Huxton | 2004-02-13 07:22:17 | Re: 7.4 - FK constraint performance |