Thread: 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
2016-05-12 10:47 GMT+02:00 Sridhar N Bamandlapally <sridhar.bn1@gmail.com>:
HiIn 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 NULLIn Oracle:declaretxt1 VARCHAR2(100) := 'ABCD';txt2 VARCHAR2(100) := NULL;txt3 VARCHAR2(100) := 'EFGH';txt VARCHAR2(100) := NULL;begintxt:= txt1 || txt2 || txt3;dbms_output.put_line (txt);end;/abcdefgh ===>return valueIn Postgresdo $$declaretxt1 text := 'ABCD';txt2 text := NULL;txt3 text := 'EFGH';txt text := NULL;begintxt:= txt1 || txt2 || txt3;raise notice '%', txt;end$$ language plpgsql;NOTICE: <NULL> ===> return valueSQL-Server also does same like OracleIs 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)
postgres=# select concat('AHOJ', NULL,'XXX');
concat
---------
AHOJXXX
(1 row)
Regards
Pavel
PleaseThanksSridharOpenText
Wrap the source columns in your line: txt:= txt1 || txt2 || txt3; in coalesce() calls Tim Clarke On 12/05/16 09:47, Sridhar N Bamandlapally wrote: > txt:= txt1 || txt2 || txt3;
Attachment
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:
Hi2016-05-12 10:47 GMT+02:00 Sridhar N Bamandlapally <sridhar.bn1@gmail.com>:HiIn 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 NULLIn Oracle:declaretxt1 VARCHAR2(100) := 'ABCD';txt2 VARCHAR2(100) := NULL;txt3 VARCHAR2(100) := 'EFGH';txt VARCHAR2(100) := NULL;begintxt:= txt1 || txt2 || txt3;dbms_output.put_line (txt);end;/abcdefgh ===>return valueIn Postgresdo $$declaretxt1 text := 'ABCD';txt2 text := NULL;txt3 text := 'EFGH';txt text := NULL;begintxt:= txt1 || txt2 || txt3;raise notice '%', txt;end$$ language plpgsql;NOTICE: <NULL> ===> return valueSQL-Server also does same like OracleIs there any way alternate we have for same behavior in PostgreSQLuse function concat http://www.postgresql.org/docs/9.5/static/functions-string.html
postgres=# select concat('AHOJ', NULL,'XXX');
concat
---------
AHOJXXX
(1 row)RegardsPavelPleaseThanksSridharOpenText
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
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:
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 concatenationHiIn 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 NULLIn Oracle:declaretxt1 VARCHAR2(100) := 'ABCD';txt2 VARCHAR2(100) := NULL;txt3 VARCHAR2(100) := 'EFGH';txt VARCHAR2(100) := NULL;begintxt:= txt1 || txt2 || txt3;dbms_output.put_line (txt);end;/abcdefgh ===>return valueIn Postgresdo $$declaretxt1 text := 'ABCD';txt2 text := NULL;txt3 text := 'EFGH';txt text := NULL;begintxt:= txt1 || txt2 || txt3;raise notice '%', txt;end$$ language plpgsql;NOTICE: <NULL> ===> return valueSQL-Server also does same like OracleIs there any way alternate we have for same behavior in PostgreSQLPleaseThanksSridharOpenText
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