Re: Specifying text to substitute for NULLs in selects

On Fri, 07 Nov 2008 15:20:24 -0500
Said Ramirez <sramirez@vonage.com> wrote:

> I think you are more after something like
>
> SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.

missing an else at least and...

wtw_drupal=# create table test.test(c1 int);
CREATE TABLE
wtw_drupal=# insert into test.test values(null);
INSERT 0 1
wtw_drupal=# insert into test.test values(1);
INSERT 0 1
wtw_drupal=# \copy (select case when c1 is null then 'NA' else c1
end from test.test) to stdout ERROR:  invalid input syntax for
integer: "NA" \copy: ERROR:  invalid input syntax for integer: "NA"

furthermore... even if c1 was text you may end up in output like:
'NA'
that will be hard to be discerned from a "normal" string.

BTW I just discovered that COPY doesn't work on view.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it