Thread: Specifying text to substitute for NULLs in selects

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
selectoutput, 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
settinga system variable to specify this? 

Thanks,

  Brent Wood

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

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
>

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

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

An even simpler way to do this is using the COALESCE function:
http://www.postgresql.org/docs/current/interactive/functions-conditional.html

SELECT COALESCE(foo, 'NA') AS foo FROM bar;

will either return the value in the field(s) "foo" or 'NA' if it is
NULL. Keep in mind that you can't mix data types, like 'NaN'::text and
32.3::float in the result.

-Mike

Mike Toews wrote:
> Keep in mind that you can't mix data types, like 'NaN'::text and
> 32.3::float in the result.

oh yeah, regarding mixing data types (in regards to the first post)...

A good exception is that you can use 'NaN' for floating point data
types, so:

    SELECT COALESCE(myval, 'NaN') as myval FROM foo;

where "myval" is a field with a floating-point data type. This maneuver
is sometimes preferred in some aggregates like sum() where you don't
want to take sums on incomplete sets since NULL is counted as 0 whereas
a single NaN value forces the resulting sum to be NaN.

There are other special floats like 'Infinity' and '-Infinity', which
can also be coalesced in for NULL float values:
http://www.postgresql.org/docs/current/interactive/datatype-numeric.html

-Mike