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
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. +