NULL concatenation

Hi

2016-05-12 10:47 GMT+02:00 Sridhar N Bamandlapally <sridhar.bn1@gmail.com>:

Hi

In migration, am facing issue with NULL concatenation in plpgsql,
by concatenating NULL between any where/position to Text / Varchar, the
total string result is setting value to NULL

*In Oracle:*

declare
txt1 VARCHAR2(100) := 'ABCD';
txt2 VARCHAR2(100) := NULL;
txt3 VARCHAR2(100) := 'EFGH';
txt VARCHAR2(100) := NULL;
begin
txt:= txt1 || txt2 || txt3;
dbms_output.put_line (txt);
end;
/

abcdefgh *===>return value*

*In Postgres*

do $$
declare
txt1 text := 'ABCD';
txt2 text := NULL;
txt3 text := 'EFGH';
txt text := NULL;
begin
txt:= txt1 || txt2 || txt3;
raise notice '%', txt;
end$$ language plpgsql;

NOTICE: <NULL> *===> return value*

SQL-Server also does same like Oracle

Is there any way alternate we have for same behavior in PostgreSQL

use function concat
http://www.postgresql.org/docs/9.5/static/functions-string.html

postgres=# select concat('AHOJ', NULL,'XXX');
concat
---------
AHOJXXX
(1 row)

Regards

Pavel

Wrap the source columns in your line:

txt:= txt1 || txt2 || txt3;

in coalesce() calls

Tim Clarke

Thanks Pavel

Great !!

I was thinking both || and CANCAT does same

Thanks again

-
Sridhar
OpenText

On Thu, May 12, 2016 at 2:22 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello Sridhar,

Have you tried the 'coalesce' function to handle the nulls?

Kind Regards,

Adam Pearson

________________________________
From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
Sent: 12 May 2016 09:47
To: PG-General Mailing List; PostgreSQL-hackers
Subject: [GENERAL] NULL concatenation

Hi

In migration, am facing issue with NULL concatenation in plpgsql,
by concatenating NULL between any where/position to Text / Varchar, the total string result is setting value to NULL

In Oracle:

declare
txt1 VARCHAR2(100) := 'ABCD';
txt2 VARCHAR2(100) := NULL;
txt3 VARCHAR2(100) := 'EFGH';
txt VARCHAR2(100) := NULL;
begin
txt:= txt1 || txt2 || txt3;
dbms_output.put_line (txt);
end;
/

abcdefgh ===>return value

In Postgres

do $$
declare
txt1 text := 'ABCD';
txt2 text := NULL;
txt3 text := 'EFGH';
txt text := NULL;
begin
txt:= txt1 || txt2 || txt3;
raise notice '%', txt;
end$$ language plpgsql;

NOTICE: <NULL> ===> return value

SQL-Server also does same like Oracle

Is there any way alternate we have for same behavior in PostgreSQL

Please

Thanks
Sridhar
OpenText

Hi Adam

we need simple concatenation of all variables(which values may come NULL or
valid-values based on functional process),

coalesce is different functionality

Thanks
Sridhar
OpenText

On Thu, May 12, 2016 at 4:56 PM, Adam Pearson <
adam.pearson@realisticgames.co.uk> wrote:

On Fri, 13 May 2016 08:45:46 +0530, Sridhar N Bamandlapally
<sridhar.bn1@gmail.com> wrote:

we need simple concatenation of all variables(which values may come NULL or
valid-values based on functional process),

coalesce is different functionality

As Pavel suggested, concat will work, but it swallows NULLs leaving no
trace of them in the output. Using coalesce *with* concat lets you
decide what a NULL will look like:

e.g.,

do $$
declare
txt1 text := 'ABCD';
txt2 text := NULL;
txt3 text := 'EFGH';
txt text := NULL;
begin

txt := coalesce( txt1, '' )
|| coalesce( txt2, 'txt2 was null' )
|| coalesce( txt3, '<null>') ;

raise notice '%', txt;
end$$ language plpgsql;

George

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general