CONCAT returns null

Started by Sterpu Victorabout 10 years ago8 messagesgeneral

victor@caido.ro

about 10 years ago

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo,
TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), '
with ', f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add' with '::VARCHARand ' with '::TEXT but the result is
still NULL.

Thank you

This is part of a bigger query that I wrote down.
SELECT d1.id AS id, d1.name AS name, COUNT(DISTINCT(f2.id)) AS
nr_spitalizari,
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' cu
', f2.nrfo, '/', TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), ' diferenta de ',
DATE(fd5.validto)-DATE(fd1.validfrom), ' zile ')) AS fise

FROM focg f
JOIN person p ON p.id = f.idpatient
JOIN focgdepartment fd1 ON fd1.idfocg = f.id
JOIN department d1 ON d1.id = fd1.iddepartment
LEFT JOIN focgdepartment fd2 ON fd2.idfocg = f.id AND fd2.validfrom <
fd1.validfrom
JOIN focgdepartment fd3 ON fd3.idfocg = f.id
JOIN department d2 ON d2.id = fd3.iddepartment
LEFT JOIN focgdepartment fd4 ON fd4.idfocg = f.id AND fd4.validfrom >
fd3.validfrom

JOIN focg f2 ON f2.idpatient = f.idpatient AND f2.id != f.id
JOIN focgdepartment fd5 ON fd5.idfocg = f2.id
LEFT JOIN focgdepartment fd6 ON fd6.idfocg = f2.id AND fd6.validfrom >
fd5.validfrom
JOIN focgdepartment fd7 ON fd7.idfocg = f2.id
LEFT JOIN focgdepartment fd8 ON fd8.idfocg = f2.id AND fd8.validfrom <
fd8.validfrom

WHERE fd2.id IS NULL AND fd4.id IS NULL AND fd6.id IS NULL AND fd8.id IS
NULL AND fd5.validto IS NOT NULL AND fd1.validfrom >= '2015-02-01' AND
fd1.validfrom <= '2016-02-29' AND
DATE(fd5.validto)-DATE(fd1.validfrom)<=30 AND
DATE(fd5.validto)-DATE(fd1.validfrom)>=0
GROUP BY d1.name, d1.id;

Charles Clavadetscher

clavadetscher@swisspug.org

about 10 years ago

In reply to: Sterpu Victor (#1)

Re: CONCAT returns null

Hello

Just a short question to that.

CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ', f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-
MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is still NULL.

Do you get NULL also if you run the query without the call to array_agg(), i.e. for all entries in your table?

Bye
Charles

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

Charles Clavadetscher

clavadetscher@swisspug.org

about 10 years ago

In reply to: Charles Clavadetscher (#2)

Re: CONCAT returns null

Hello again

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Sonntag, 28. Februar 2016 11:24
To: 'Sterpu Victor' <victor@caido.ro>; 'PostgreSQL General' <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] CONCAT returns null

Hello

Just a short question to that.

CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ', f2.nrfo, TO_CHAR(fd7.validfrom,

'YYYY-

MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is still NULL.

Do you get NULL also if you run the query without the call to array_agg(), i.e. for all entries in your table?

Forget it. Should not depend on that. If you have any null entry it will be added in the array as NULL.
Bye
Charles

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

Andreas Kretschmer

akretschmer@spamfence.net

about 10 years ago

In reply to: Sterpu Victor (#1)

Re: CONCAT returns null

Sterpu Victor <victor@caido.ro> wrote:

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR
(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ',
f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE
(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is
still NULL.

Thank you

NULL concat with a value returns NULL. You can avoid that using
COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082ï¿½, E 13.56889ï¿½

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

Charles Clavadetscher

clavadetscher@swisspug.org

about 10 years ago

In reply to: Andreas Kretschmer (#4)

Re: CONCAT returns null

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Sonntag, 28. Februar 2016 11:30
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] CONCAT returns null

Sterpu Victor <victor@caido.ro> wrote:

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR
(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ',
f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE
(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is
still NULL.

Thank you

NULL concat with a value returns NULL. You can avoid that using
COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

That was my first thought, too, but:

db=> select concat(null,'sssdf',null,'dfg',NULL);
concat
----------
sssdfdfg
(1 row)

Bye Charles

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

Sterpu Victor

victor@caido.ro

about 10 years ago

In reply to: Sterpu Victor (#1)

Re: CONCAT returns null

The problem was from PG Admin that is not displaing cells with a high
amount of data.
In the application the query is working well.

------ Original Message ------
From: "Sterpu Victor" <victor@caido.ro>
To: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 28/2/2016 12:02:47 PM
Subject: [GENERAL] CONCAT returns null

Show quoted text

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo,
TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), '
with ', f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add' with '::VARCHARand ' with '::TEXT but the result
is still NULL.

Thank you

This is part of a bigger query that I wrote down.
SELECT d1.id AS id, d1.name AS name, COUNT(DISTINCT(f2.id)) AS
nr_spitalizari,
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), '
cu ', f2.nrfo, '/', TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), ' diferenta
de ', DATE(fd5.validto)-DATE(fd1.validfrom), ' zile ')) AS fise

FROM focg f
JOIN person p ON p.id = f.idpatient
JOIN focgdepartment fd1 ON fd1.idfocg = f.id
JOIN department d1 ON d1.id = fd1.iddepartment
LEFT JOIN focgdepartment fd2 ON fd2.idfocg = f.id AND fd2.validfrom <
fd1.validfrom
JOIN focgdepartment fd3 ON fd3.idfocg = f.id
JOIN department d2 ON d2.id = fd3.iddepartment
LEFT JOIN focgdepartment fd4 ON fd4.idfocg = f.id AND fd4.validfrom >
fd3.validfrom

JOIN focg f2 ON f2.idpatient = f.idpatient AND f2.id != f.id
JOIN focgdepartment fd5 ON fd5.idfocg = f2.id
LEFT JOIN focgdepartment fd6 ON fd6.idfocg = f2.id AND fd6.validfrom >
fd5.validfrom
JOIN focgdepartment fd7 ON fd7.idfocg = f2.id
LEFT JOIN focgdepartment fd8 ON fd8.idfocg = f2.id AND fd8.validfrom <
fd8.validfrom

WHERE fd2.id IS NULL AND fd4.id IS NULL AND fd6.id IS NULL AND fd8.id
IS NULL AND fd5.validto IS NOT NULL AND fd1.validfrom >= '2015-02-01'
AND fd1.validfrom <= '2016-02-29' AND
DATE(fd5.validto)-DATE(fd1.validfrom)<=30 AND
DATE(fd5.validto)-DATE(fd1.validfrom)>=0
GROUP BY d1.name, d1.id;

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt
confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt
mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj,
este interzis sa actionati in baza acestor informatii. Citirea,
copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a
informatiei continute in acest mesaj constituie o incalcare a legii.
Daca ati primit mesajul din greseala, va rugam sa il distrugeti,
anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat
faptul ca posta electronica este un mod sigur si lipsit de erori de
transmitere a informatiilor, este responsabilitatea dvs. sa va
asigurati ca mesajul (inclusiv documentele alaturate lui) este validat
si autorizat spre a fi utilizat in mediul dvs.

Adrian Klaver

adrian.klaver@aklaver.com

about 10 years ago

In reply to: Andreas Kretschmer (#4)

Re: CONCAT returns null

On 02/28/2016 02:30 AM, Andreas Kretschmer wrote:

Sterpu Victor <victor@caido.ro> wrote:

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo, TO_CHAR
(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' with ',
f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), DATE(fd5.validto)-DATE
(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is
still NULL.

Thank you

NULL concat with a value returns NULL. You can avoid that using
COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

http://www.postgresql.org/docs/9.5/interactive/functions-string.html
"
concat(str "any" [, str "any" [, ...] ]) text Concatenate the text
representations of all the arguments. NULL arguments are ignored.
"

Andreas

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Andreas Kretschmer

akretschmer@spamfence.net

about 10 years ago

In reply to: Adrian Klaver (#7)

Re: CONCAT returns null

Adrian Klaver <adrian.klaver@aklaver.com> wrote:

NULL concat with a value returns NULL. You can avoid that using
COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

http://www.postgresql.org/docs/9.5/interactive/functions-string.html
"
concat(str "any" [, str "any" [, ...] ]) text Concatenate the text
representations of all the arguments. NULL arguments are ignored.
"

you are right, my fault :-(, muddled with the || - operator.

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