Re: Specifying text to substitute for NULLs in selects

I think you are more after something like

SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.
   -Said

Ivan Sergio Borgonovo wrote:
> On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
> pcreso@pcreso.com wrote:
>
>  >
>  > Hi,
>  >
>  > I can specify the text used to represent a null value in output
>  > from copy, but I'd like to do something similar is select output,
>  > eg: all NULL values are represented by NA or NaN.
>  >
>  > I can't find anything in the docs about this.
>  >
>  > This could be managed using case statements around all the columns
>  > in the query, but is there a simpler way, like setting a system
>  > variable to specify this?
>
> wtw_drupal=# create schema test;
> CREATE SCHEMA
> wtw_drupal=# create table test.test(c1 text);
> CREATE TABLE
> wtw_drupal=# insert into test.test values(null);
> INSERT 0 1
> wtw_drupal=# insert into test.test values('test');
> INSERT 0 1
> wtw_drupal=# \copy test.test to stdout null as 'BANANA'
> BANANA
> test
> wtw_drupal=# drop schema test cascade;
> NOTICE:  drop cascades to table test.test
> DROP SCHEMA
>
> everything clearly explained in the COPY manual:
> http://www.postgresql.org/docs/8.1/static/sql-copy.html
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>