Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOFU3-0005d4-Rj for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2013 22:51:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UOFU3-0001Dq-8z for pgsql-sql@arkaria.postgresql.org; Fri, 05 Apr 2013 22:51:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOFU2-0001Dd-C5 for pgsql-sql@postgresql.org; Fri, 05 Apr 2013 22:51:46 +0000 Received: from dub0-omc2-s19.dub0.hotmail.com ([157.55.1.158]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOFTy-0001KB-7r for pgsql-sql@postgresql.org; Fri, 05 Apr 2013 22:51:46 +0000 Received: from DUB116-W17 ([157.55.1.136]) by dub0-omc2-s19.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Fri, 5 Apr 2013 15:51:38 -0700 X-EIP: [fYrJ6OG+oXY+m+rm1Skd7Q99+Fs3g0Ad] X-Originating-Email: [kong_mansatiansin@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_04806b22-2633-45a5-8ec4-e14ea01f70ea_" From: Kong Man To: "pgsql-sql@postgresql.org" Subject: Re: Data Loss from SQL SELECT (vs. COPY/pg_dump) Date: Fri, 5 Apr 2013 15:51:38 -0700 Importance: Normal In-Reply-To: References: , MIME-Version: 1.0 X-OriginalArrivalTime: 05 Apr 2013 22:51:38.0198 (UTC) FILETIME=[218CEF60:01CE3250] X-Pg-Spam-Score: -4.3 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_04806b22-2633-45a5-8ec4-e14ea01f70ea_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This seems to answer my question. I completely forgot about the behavior o= f NULL value in the text concatenation. =0A= =0A= http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-Q= UOTE-LITERAL-EXAMPLE=0A= =0A= =0A= =0A= Because quote_literal is labelled STRICT=2C it=0A= will always return null when called with a null argument. In the above exam= ple=2C=0A= if newvalue or keyvalue were null=2C the=0A= entire dynamic query string would become null=2C leading to an error from E= XECUTE. You=0A= can avoid this problem by using the quote_nullable function=2C=0A= which works the same as quote_literal except that=0A= when called with a null argument it returns the string NULL. For=0A= example=2C=0A= =0A= = --_04806b22-2633-45a5-8ec4-e14ea01f70ea_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

This seems to answer my question= . =3B I completely forgot about the behavior of NULL =3B value in t= he text concatenation.

=0A= =0A=

http://www.postgres= ql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPL= E

=0A= =0A=

=3B

=0A= =0A=

Because =3Bquote_literal =3Bis labelled&n= bsp=3BSTRICT=2C it=0A= will always return null when called with a null argument. In the above exam= ple=2C=0A= if =3Bnewvalue =3Bor =3Bkeyvalue =3Bwere null=2C the=0A= entire dynamic query string would become null=2C leading to an error from =3BEXECUTE. You=0A= can avoid this problem by using the&n= bsp=3Bquote_nullable =3Bfunction=2C=0A= which works the same as =3Bquote_literal =3Bexcept that=0A= when called with a null argument it returns the string =3BNULL. For=0A= example=2C

=0A= =0A=

= --_04806b22-2633-45a5-8ec4-e14ea01f70ea_--