From: Kong Man <kong_mansatiansin@hotmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Data Loss from SQL SELECT (vs. COPY/pg_dump)
Date: Fri, 5 Apr 2013 15:51:38 -0700
Message-ID: <DUB116-W175B9735F113E336895E098BDA0@phx.gbl> (raw)
In-Reply-To: <DUB116-W8909170CBFD4431563CE808BDA0@phx.gbl>
References: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
<DUB116-W8909170CBFD4431563CE808BDA0@phx.gbl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
This seems to answer my question. I completely forgot about the behavior of NULL value in the text concatenation.
http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
Because quote_literal is labelled STRICT, it
will always return null when called with a null argument. In the above example,
if newvalue or keyvalue were null, the
entire dynamic query string would become null, leading to an error from EXECUTE. You
can avoid this problem by using the quote_nullable function,
which works the same as quote_literal except that
when called with a null argument it returns the string NULL. For
example,
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kong_mansatiansin@hotmail.com
Subject: Re: Data Loss from SQL SELECT (vs. COPY/pg_dump)
In-Reply-To: <DUB116-W175B9735F113E336895E098BDA0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox