Thread: concatenation operator || with "null" array

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

stroncococcus wrote:
> Ok, solved the problem with COALESCE.

Hm, I not really solved it ... just solved it for text columns, but not
for integer arrays.
I can use this for text
COALESCE(textcol, '') || 'str '
but how do I use this for integers?
Is there a way to create an empty integer array and do such a thing ...
COALESCE(intarraycol, *empty int array*) || 5

Ok, solved the problem with COALESCE.

NULL concatenated to anything is NULL.  Try this:

UPDATE test SET myint = COALESCE(myint || ARRAY[123], ARRAY[123]) WHERE
id = 1;

Or:

UPDATE test SET myint =
   CASE WHEN myint IS NULL THEN ARRAY[123]
   ELSE myint || ARRAY[123]
   END
WHERE id = 1;

An empty array can be displayed as ARRAY[NULL], but defaults to type
TEXT.  An explicit empty integer array would be ARRAY[NULL]::INTEGER[].
NULL arrays are not handled entirely consistently, though.  Sometimes it
acts like a NULL, and sometimes it acts like a container of NULL.



--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of stroncococcus
Sent: Wednesday, December 06, 2006 5:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] concatenation operator || with "null" array

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

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>,