Re: Specifying text to substitute for NULLs in selects
От | Brent Wood |
---|---|
Тема | Re: Specifying text to substitute for NULLs in selects |
Дата | |
Msg-id | 49158E840200007B00016B14@gwia1.ham.niwa.co.nz обсуждение исходный текст |
Ответы |
Re: Specifying text to substitute for NULLs in selects
(Ivan Sergio Borgonovo <mail@webthatworks.it>)
Re: Specifying text to substitute for NULLs in selects (Adrian Klaver <aklaver@comcast.net>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления:
Следующее
От: Ivan Sergio BorgonovoДата:
Сообщение: Re: Specifying text to substitute for NULLs in selects