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@xxxxxxxxxx
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@xxxxxxxxxx";>bruce@xxxxxxxxxx</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@xxxxxxxxxx";>bruce@xxxxxxxxxx</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>,
