Re: Data Loss from SQL SELECT (vs. COPY/pg_dump) - Mailing list pgsql-sql
| From | Kong Man |
|---|---|
| Subject | Re: Data Loss from SQL SELECT (vs. COPY/pg_dump) |
| Date | |
| Msg-id | DUB116-W175B9735F113E336895E098BDA0@phx.gbl Whole thread Raw |
| In response to | Data Loss from SQL SELECT (vs. COPY/pg_dump) (Kong Man <kong_mansatiansin@hotmail.com>) |
| List | pgsql-sql |
<div dir="ltr">This seems to answer my question. I completely forgot about the behavior of NULL value in the text concatenation.<br/><br /><p class="MsoNormal"><a href="http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE">http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE</a><p class="MsoNormal"> <pclass="MsoNormal"><span style="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white">Because<span class="apple-converted-space"> </span></span><code><span style="font-size:9.0pt;color:black;background:white">quote_literal</span></code><span class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white"> </span></span><span style="font-size:9.0pt; font-family:"Verdana","sans-serif";color:black;background:white">is labelled<span class="apple-converted-space"> </span></span><tt><spanstyle="font-size: 9.0pt;color:black;background:white">STRICT</span></tt><span style="font-size: 9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">, it will always return null when called with a nullargument. In the above example, if<span class="apple-converted-space"> </span></span><tt><span style="font-size:9.0pt;color:black;background:white">newvalue</span></tt><spanclass="apple-converted-space"><span style="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white"> </span></span><span style="font-size:9.0pt; font-family:"Verdana","sans-serif";color:black;background:white">or<span class="apple-converted-space"> </span></span><tt><spanstyle="font-size: 9.0pt;color:black;background:white">keyvalue</span></tt><span class="apple-converted-space"><span style="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white"> </span></span><span style="font-size:9.0pt; font-family:"Verdana","sans-serif";color:black;background:white">were null, the entire dynamic query string would becomenull, leading to an error from<span class="apple-converted-space"> </span></span><tt><span style="font-size: 9.0pt;color:black;background:white">EXECUTE</span></tt><span style="font-size: 9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">. You can avoid this problem by using the<span class="apple-converted-space"> </span></span><code><span style="font-size:9.0pt;color:black;background:white">quote_nullable</span></code><span class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white"> </span></span><span style="font-size:9.0pt; font-family:"Verdana","sans-serif";color:black;background:white">function, which works the same as<span class="apple-converted-space"> </span></span><code><span style="font-size:9.0pt;color:black;background:white">quote_literal</span></code><span class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; color:black;background:white"> </span></span><span style="font-size:9.0pt; font-family:"Verdana","sans-serif";color:black;background:white">except that when called with a null argument it returnsthe string<span class="apple-converted-space"> </span></span><tt><span style="font-size: 9.0pt;color:black;background:white">NULL</span></tt><span style="font-size: 9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">. For example,</span><span style="color:#1F497D"></span><br/><br /></div>