Re: [GENERAL] Specifying text to substitute for NULLs in selects
Thanks Adrian, That's perfect!! Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Adrian Klaver <[EMAIL PROTECTED]> 11/08/08 1:49 PM >>> 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 '', attr, attr, attr, '' from ;" | sed > 's/||/|NA|/' | sed 's/|//' | sed 's/|//' > 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 [EMAIL PROTECTED] NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
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 '', attr, attr, attr, '' from ;" | sed > 's/||/|NA|/' | sed 's/|//' | sed 's/|//' > 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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Sat, 08 Nov 2008 13:05:08 +1300 "Brent Wood" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 '', attr, attr, attr, '' from ;" | sed
's/||/|NA|/' | sed 's/|//' | sed 's/|//' > 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 <[EMAIL PROTECTED]> 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)
> [EMAIL PROTECTED] 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Fri, 07 Nov 2008 15:20:24 -0500 Said Ramirez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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)
[EMAIL PROTECTED] 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
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
[EMAIL PROTECTED] 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
[GENERAL] 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 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? Thanks, Brent Wood -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
