How to COALESCE for empty strings and NULL values?

SqlPostgresqlCoalesce

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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 ,'').

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMikeCWView Question on Stackoverflow
Solution 1 - SqlDwayne TowellView Answer on Stackoverflow