Thanks Adrian, That's perfect!! Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Adrian Klaver <aklaver@xxxxxxxxxxx> 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 '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@xxxxxxxxxxx NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
