coalesce function

Hi,

http://www.postgresql.org/docs/9.1/static/functions-conditional.html describes
NULLIF, when combined with COALESCE it should answer your request.

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!
They need to add TRUNCATE PARTITION in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table

2013/6/20 itishree sukla <itishree.sukla@gmail.com>

Torsdag 20. juni 2013 21.45.02 skrev itishree sukla:

Hi All,

I am using coalesce(firstname,lastname), to get the result if first name is
'NULL' it will give me lastname or either way. I am having data like
instead of NULL, blank null ( i mean something like '' ) for which
coalesce is not working, is there any workaround or other function
available in postgresql, please do let me know.

CASE WHEN firstname NOT IN (NULL, '') THEN firstname ELSE lastname END;

regards, Leif

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

itishree sukla wrote

Hi All,

I am using coalesce(firstname,lastname), to get the result if first name
is
'NULL' it will give me lastname or either way. I am having data like
instead of NULL, blank null ( i mean something like '' ) for which
coalesce is not working, is there any workaround or other function
available in postgresql, please do let me know.

Regards,
Itishree

This is the solution I am currently using in my work:

Runs in 9.0

CREATE OR REPLACE FUNCTION coalesce_emptystring(VARIADIC in_ordered_actual
varchar[])
RETURNS varchar
AS $$

SELECT input
FROM ( SELECT unnest($1) AS input ) src
WHERE input IS NOT NULL AND input <> ''
LIMIT 1;

$$
LANGUAGE sql
STABLE
;

Same usage syntax as the built-in COALESCE but skips NULL and the
empty-string. Note a string with only whitespace (i.e., ' ') is not
considered empty.

The problem with the "CASE" example provided is that while it works in the
specific case you are solving it does not readily generalize to more than 2
inputs.

Are you positive the "lastname" will always have a value? You should
consider a last-resort default to ensure that the column never returns a
NULL.

coalesce_emptystring(firstname, lastname, 'Name Unknown')

--
View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760205.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

On Fri, Jun 21, 2013 at 7:36 AM, David Johnston <polobo@yahoo.com> wrote:

SELECT input
FROM ( SELECT unnest($1) AS input ) src
WHERE input IS NOT NULL AND input <> ''
LIMIT 1;

Does this guarantee the order of the results returned? Using LIMIT
without ORDER BY is something I've learned to avoid.

ChrisA

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

Chris Angelico wrote

On Fri, Jun 21, 2013 at 7:36 AM, David Johnston &lt;

polobo@

&gt; wrote:

SELECT input
FROM ( SELECT unnest($1) AS input ) src
WHERE input IS NOT NULL AND input <> ''
LIMIT 1;

Does this guarantee the order of the results returned? Using LIMIT
without ORDER BY is something I've learned to avoid.

ChrisA

I have thought about this and while I'm not 100% positive on the guarantee
the fact the input data is small means the planner should not be re-ordering
"src" in order to apply the where clause (if it would anyway...I think
re-ordering may only happen during joins). To my knowledge the result of
unnest returns in the same order as the array so "src" already has an
implicit "ORDER BY" attached to it. It is only when return physical
relation data is the order undefined. Arrays and "VALUES" both are returned
in the order defined.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760342.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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