Re: How to avoid nulls while writing string for dynamic query
От | Kumar |
---|---|
Тема | Re: How to avoid nulls while writing string for dynamic query |
Дата | |
Msg-id | 011101c3f211$c78d7c10$7502a8c0@hdsc.com обсуждение исходный текст |
Ответ на | How to avoid nulls while writing string for dynamic query ("Kumar" <sgnerd@yahoo.com.sg>) |
Ответы |
Re: How to avoid nulls while writing string for dynamic query
|
Список | pgsql-sql |
Thanks Tomasz Myrta. It is wonderful. I am still amazed from where you guys knowing the options like quote_literal, etc. Kumar ----- Original Message ----- From: "Tomasz Myrta" <jasiek@klaster.net> To: "Kumar" <sgnerd@yahoo.com.sg> Cc: "psql" <pgsql-sql@postgresql.org> Sent: Friday, February 13, 2004 1:37 PM Subject: Re: [SQL] 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
В списке pgsql-sql по дате отправления: