Re: BUG #2131: SQL Query Bug ? - Mailing list pgsql-bugs

From Michael Fuhr
Subject Re: BUG #2131: SQL Query Bug ?
Date
Msg-id 20051227172806.GA70199@winnie.fuhr.org
Whole thread Raw
In response to BUG #2131: SQL Query Bug ?  ("kenichi nakanishi" <kenichi_nakanishi@fukuicsk.co.jp>)
List pgsql-bugs
On Mon, Dec 26, 2005 at 03:47:36PM +0000, kenichi nakanishi wrote:
> I found something strange result when using a following sql sentence,
> "select xxx || ' / ' || yyyy || ' / ' || zzzz as aaa from TABLE",
> sometime I could get empty results.
> When using same scentence on linux platform, I could get correct results.
> So I think it's a bug on windows version.

Do the Linux and Windows platforms have the same data?  Might any
of the columns on the Windows system be NULL?  Concatenating anything
with NULL results in NULL, so that could be the problem.

test=> CREATE TABLE foo (col1 text, col2 text);
CREATE TABLE
test=> INSERT INTO foo (col1, col2) VALUES ('aaa', 'bbb');
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES ('ccc', NULL);
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES (NULL, 'ddd');
INSERT 0 1
test=> SELECT col1, col2, col1 || col2 FROM foo;
 col1 | col2 | ?column?
------+------+----------
 aaa  | bbb  | aaabbb
 ccc  |      |
      | ddd  |
(3 rows)

If you want to treat NULL as an empty string then use COALESCE:

test=> SELECT col1, col2, COALESCE(col1, '') || COALESCE(col2, '') FROM foo;
 col1 | col2 | ?column?
------+------+----------
 aaa  | bbb  | aaabbb
 ccc  |      | ccc
      | ddd  | ddd
(3 rows)

--
Michael Fuhr

pgsql-bugs by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: BUG #2124: Error "relation with OID ... does not exist" when
Next
From: Bruce Momjian
Date:
Subject: Re: BUG #2128: unable to install win32 version of pgsql 8.1.1