Re: concatenation operator || with "null" array - Mailing list pgsql-general

The question of concatentation using NULLs comes up enough that I have
added an item to an existing FAQ entry for it, patch attached.

---------------------------------------------------------------------------

stroncococcus wrote:
> Hello!
>
> When I try to fill an array with the concatenation operator, like
> UPDATE test SET myint = myint || ARRAY[123] WHERE id = 1
> that before that statement was null, then it is also null after that
> statement.
> But if there is already something in that array and I execute that
> statement, then everything works fine and one can find the 123 there,
> too.
> Is this the normal behavior? Is there a way to "concatenate" to null
> arrays as well, or do I have to test this inside my script, and if it
> is null fill it normal for the first time?
>
> Best regards,
> Kai
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: FAQ.html
===================================================================
RCS file: /cvsroot/pgsql/doc/src/FAQ/FAQ.html,v
retrieving revision 1.360
retrieving revision 1.361
diff -c -c -r1.360 -r1.361
*** FAQ.html    5 Dec 2006 23:13:41 -0000    1.360
--- FAQ.html    11 Dec 2006 22:44:53 -0000    1.361
***************
*** 10,16 ****
    alink="#0000ff">
      <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>

!     <P>Last updated: Tue Dec  5 18:13:32 EST 2006</P>

      <P>Current maintainer: Bruce Momjian (<A href=
      "mailto:bruce@momjian.us">bruce@momjian.us</A>)
--- 10,16 ----
    alink="#0000ff">
      <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>

!     <P>Last updated: Mon Dec 11 17:44:33 EST 2006</P>

      <P>Current maintainer: Bruce Momjian (<A href=
      "mailto:bruce@momjian.us">bruce@momjian.us</A>)
***************
*** 86,93 ****
      searches and case-insensitive regular expression searches? How do I
      use an index for case-insensitive searches?<BR>
       <A href="#item4.9">4.9</A>) In a query, how do I detect if a field
!     is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
!     NULL</SMALL> or not?<BR>
       <A href="#item4.10">4.10</A>) What is the difference between the
      various character types?<BR>
       <A href="#item4.11.1">4.11.1</A>) How do I create a
--- 86,93 ----
      searches and case-insensitive regular expression searches? How do I
      use an index for case-insensitive searches?<BR>
       <A href="#item4.9">4.9</A>) In a query, how do I detect if a field
!     is <SMALL>NULL</SMALL>?  How do I concatenate possible <SMALL>NULL</SMALL>s?
!     How can I sort on whether a field is <SMALL> NULL</SMALL> or not?<BR>
       <A href="#item4.10">4.10</A>) What is the difference between the
      various character types?<BR>
       <A href="#item4.11.1">4.11.1</A>) How do I create a
***************
*** 823,832 ****
      identical values that differ only in case. To force a particular
      case to be stored in the column, use a <SMALL>CHECK</SMALL>
      constraint or a trigger.</P>
!
      <H3 id="item4.9">4.9) In a query, how do I detect if a field
!     is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
!     NULL</SMALL> or not?</H3>

      <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
      NOT NULL</SMALL>, like this:</P>
--- 823,836 ----
      identical values that differ only in case. To force a particular
      case to be stored in the column, use a <SMALL>CHECK</SMALL>
      constraint or a trigger.</P>
!
!      <A href="#item4.9">4.9</A>) In a query, how do I detect if a field
!     is <SMALL>NULL</SMALL>?  How do I concatenate possible <SMALL>NULL</SMALL>s?
!     How can I sort on whether a field is <SMALL> NULL</SMALL> or not?<BR>
!
      <H3 id="item4.9">4.9) In a query, how do I detect if a field
!     is <SMALL>NULL</SMALL>?  How do I concatenate possible <SMALL>NULL</SMALL>s?
!     How can I sort on whether a field is <SMALL> NULL</SMALL> or not?</H3>

      <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
      NOT NULL</SMALL>, like this:</P>
***************
*** 837,842 ****
--- 841,853 ----
     WHERE col IS NULL;
  </PRE>

+    <P>To concatentate with possible <SMALL>NULL</SMALL>s, use <I>COALESCE()</I>,
+    like this:</P>
+ <PRE>
+    SELECT COALESCE(col1, '') || COALESCE(col2, '')
+    FROM tab
+ </PRE>
+
     <P>To sort by the <SMALL>NULL</SMALL> status, use the <SMALL>IS NULL</SMALL>
     and <SMALL>IS NOT NULL</SMALL> modifiers in your <SMALL>ORDER BY</SMALL> clause.
     Things that are <I>true</I> will sort higher than things that are <I>false</I>,