SELECT Field1 || Field2 FROM Table
Hello all,
I have PostgreSQL 7.1.1 installed on a RedHat 7.1 server.
When running the following query "SELECT Field1 || Field2 AS Result FROM Table"
the result is NULL when Field2 is NULL.
Same as if I use a PL/pgSQL function to concatenate Filed 1 || Field2.
Did I miss something?
Regards,
Jean-Michel POURE, pgAdmin development team
Yes, that's correct behaviour. Any operation on null will yield null.
What you need to do:
select coalesce(field1,'') || coalesce(field2,'') ...
-alex
On Wed, 13 Jun 2001, Jean-Michel POURE wrote:
Show quoted text
Hello all,
I have PostgreSQL 7.1.1 installed on a RedHat 7.1 server.
When running the following query "SELECT Field1 || Field2 AS Result FROM Table"
the result is NULL when Field2 is NULL.Same as if I use a PL/pgSQL function to concatenate Filed 1 || Field2.
Did I miss something?
Regards,
Jean-Michel POURE, pgAdmin development team---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Hello Robert (Bob?),
Thank you for your answer. I will surely make a wide use the COALESCE
function in my scripts.
I also noticed the same behaviour in PL/pgSQL:
CREATE FUNCTION "xxxxxxxxxxxxxx" (text, text)
RETURNS text
AS 'BEGIN
RETURN $1 || ' ' || $2;
END;
'
LANGUAGE 'plpgsql'
Correct me if I am wrong:
It seems that a NULL value is not passed to the function ...
... or is it that a NULL value is not taken into account by PL/pgSQL.
Thank you all for the COALESCE trick.
Greetings from Jean-Michel POURE, Paris, France
pgAdmin development team
Import Notes
Reply to msg id not found: 4.2.0.58.20010614084801.01f27ad0@pop.freesurf.fr | Resolved by subject fallback
plpgsql is a bit tricky.
In 7.0, if an argument to a function was null, function did not execute at
all, and the result was assumed null.
In 7.1, you can control this behaviour by declaring function as 'strict'
or 'nostrict'. ex: create function(...) as '...' with (strict) gets old
behaviour, nostrict will do what you want.
On Thu, 14 Jun 2001, Jean-Michel POURE wrote:
Show quoted text
Hello Robert (Bob?),
Thank you for your answer. I will surely make a wide use the COALESCE
function in my scripts.
I also noticed the same behaviour in PL/pgSQL:CREATE FUNCTION "xxxxxxxxxxxxxx" (text, text)
RETURNS text
AS 'BEGINRETURN $1 || ' ' || $2;
END;
'
LANGUAGE 'plpgsql'Correct me if I am wrong:
It seems that a NULL value is not passed to the function ...
... or is it that a NULL value is not taken into account by PL/pgSQL.Thank you all for the COALESCE trick.
Greetings from Jean-Michel POURE, Paris, France
pgAdmin development team---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster