select NULL||'abc' returns empty string (or NULL)

Greetings, Holger!

At 27.02.2002, 14:25, you wrote:
HM> I suppose that PostgreSQL insists that a NULL value cannot be
HM> concatenated with a string. Can I cast this somehow? I noticed that
HM> feature when I did a

HM> select * from tab where col1||col2 ilike '%bla%'

HM> and it did't find rows where one of the columns was NULL. Any ideas for
HM> workarounds?
Yes:
SELECT * FROM tab WHERE coalesce(col1, '')||coalesce(col2, '') ILIKE '%bla%';

--
Yours, Alexey V. Borzov, Webmaster of RDW.ru

On Wed, Feb 27, 2002 at 12:25:54PM +0100, Holger Marzen wrote:

I suppose that PostgreSQL insists that a NULL value cannot be
concatenated with a string. Can I cast this somehow? I noticed that
feature when I did a

select * from tab where col1||col2 ilike '%bla%'

and it did't find rows where one of the columns was NULL. Any ideas for
workarounds?

You're look for the coalesce function.

coalesce(NULL,a) = a
coalesce(val,a) = val

try:

select * from tab where col1||coalesce(col2,'') ilike '%bla%';

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

On Wed, 2002-02-27 at 11:25, Holger Marzen wrote:

I suppose that PostgreSQL insists that a NULL value cannot be
concatenated with a string. Can I cast this somehow? I noticed that
feature when I did a

select * from tab where col1||col2 ilike '%bla%'

and it did't find rows where one of the columns was NULL. Any ideas for
workarounds?

Use COALESCE():
select * from tab where coalesce(col1,'') | coalesce(col2,'') ilike
'%bla%'

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"If we confess our sins, he is faithful and just to
forgive us our sins, and to cleanse us from all
unrighteousness." I John 1:9

postgresql returns null for null||'abc'.
null means 'unknowm' so if you concatenate something known
with something unknown, the result is unknown.

You probably want to do :

select *
from tab
where (case when col1=null then '' else col1)
||(case when col2=null then '' else col2) ilike '%bla%';

Steve.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Holger Marzen
Envoye : mercredi 27 fevrier 2002 12:26
A : pgsql-general@postgresql.org
Objet : [GENERAL] select NULL||'abc' returns empty string (or NULL)

I suppose that PostgreSQL insists that a NULL value cannot be
concatenated with a string. Can I cast this somehow? I noticed that
feature when I did a

select * from tab where col1||col2 ilike '%bla%'

and it did't find rows where one of the columns was NULL. Any ideas for
workarounds?

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&amp;search=0xB5A1AFE1

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org