Search Postgresql Archives

Re: Specifying text to substitute for NULLs in selects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux