How to COALESCE for empty strings and NULL values?
57,733
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 ,'')
.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Author by
Admin
Updated on July 05, 2022Comments
-
Admin about 2 years
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?
-
venkat over 6 yearsCoalesce is not defined it is coming, You have any idea about this ??