How to COALESCE for empty strings and NULL values?

sql postgresql coalesce
MikeCW picture MikeCW · Mar 14, 2014 · Viewed 47.6k times · Source

I'm trying to make the fastest COALESCE() that accepts two or more arguments, and returns the first non-null AND non-empty ("") value.

I'm using this:

CREATE OR REPLACE FUNCTION coalescenonempty(VARIADIC in_ordered_actual varchar[])
RETURNS varchar AS $$
  SELECT i
  FROM (SELECT unnest($1) AS i) t
  WHERE i IS NOT NULL AND i <> ''
  LIMIT 1;
$$ LANGUAGE sql;

It's pretty fast, but still nowhere as fast as COALESCE or CASE WHEN statements.

What do you think?

Answer

Dwayne Towell picture Dwayne Towell · Mar 14, 2014

Do not create a user function is you want speed. Instead of this:

coalescenonempty(col1,col2||'blah',col3,'none');

do this:

COALESCE(NULLIF(col1,''),NULLIF(col2||'blah',''),NULLIF(col3,''),'none');

That is, for each non-constant parameter, surround the actual parameter with NULLIF( x ,'').

Related questions

How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select …

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More
Using COALESCE to handle NULL values in PostgreSQL

I have the following query SELECT DISTINCT pt.incentive_marketing, pt.incentive_channel, pt.incentive_advertising FROM test.pricing pt WHERE pt.contract_id = 90000 group by 1,2,3 order by pt.incentive_marketing; The above query returns the o/p as shown …

Read More