Обсуждение: Re: Specifying text to substitute for NULLs in selects

Thanks guys,

I'm aware of those options, what I was wondering was if there is a more generic way,
for example the Empress RDBMS allows 'set MSNULLVALUE "NA"', and all NULLs
will from then on be output as NA.

The COPY option is closest to a generic setting, but doesn't work with a select query,
just a table dump.

I guess something like the following will work from the shell, although it is hardly elegant :-)...

psql -d DB -Atc "select 'xxxx', attr, attr, attr, 'xxxx' from ....;" | sed 's/||/|NA|/' | sed 's/xxxx|//' | sed
's/|xxxx//'> data.txt 

Slightly simpler than the case statement approach in Postgres is COALESCE()

eg:  select COALESCE(attr,'NA') as attr from table;

but this still needs to be applied to every column in the outout which may have nulls. rather than a generic one off
setting.A view using COALESCE() may be the easiest way for users to have this capability automatically.. 

Thanks,

   Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Said Ramirez <sramirez@vonage.com> 11/08/08 12:34 PM >>>
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
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

On Sat, 08 Nov 2008 13:05:08 +1300
"Brent Wood" <b.wood@niwa.co.nz> wrote:

> Thanks guys,
>
> I'm aware of those options, what I was wondering was if there is a
> more generic way, for example the Empress RDBMS allows 'set
> MSNULLVALUE "NA"', and all NULLs will from then on be output as NA.
>
> The COPY option is closest to a generic setting, but doesn't work
> with a select query, just a table dump.

\copy (select ....) to ...
works.

As written in my 2nd post.

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

On Friday 07 November 2008 4:05:08 pm Brent Wood wrote:
> Thanks guys,
>
> I'm aware of those options, what I was wondering was if there is a more
> generic way, for example the Empress RDBMS allows 'set MSNULLVALUE "NA"',
> and all NULLs will from then on be output as NA.
>
> The COPY option is closest to a generic setting, but doesn't work with a
> select query, just a table dump.
>
> I guess something like the following will work from the shell, although it
> is hardly elegant :-)...
>
> psql -d DB -Atc "select 'xxxx', attr, attr, attr, 'xxxx' from ....;" | sed
> 's/||/|NA|/' | sed 's/xxxx|//' | sed 's/|xxxx//' > data.txt
>
> Slightly simpler than the case statement approach in Postgres is COALESCE()
>
> eg:  select COALESCE(attr,'NA') as attr from table;
>
> but this still needs to be applied to every column in the outout which may
> have nulls. rather than a generic one off setting. A view using COALESCE()
> may be the easiest way for users to have this capability automatically..
>
> Thanks,
>
>    Brent Wood
>
>

Using psql
http://www.postgresql.org/docs/8.2/interactive/app-psql.html
lfnw=# \a\t\f ','\pset null 'NA'
Output format is unaligned.
Showing only tuples.
Field separator is ",".
Null display is "NA".
lfnw=# SELECT null,1;
NA,1


--
Adrian Klaver
aklaver@comcast.net