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