In PostgreSQL, regexp_replace behavior alters if it is either replacing a white-spaced string in a string array with a blank string or null.

1 - Replacing a white-spaced string with blank

SELECT regexp_replace(UNNEST('{NULL, "ABC", " "}'::text[]), '^\s $', '', 'g')

Expected result: {NULL,"ABC",""}

Result: {NULL,"ABC",""} <-- Working as it should

2 - Replacing a white-spaced string with null

SELECT regexp_replace(UNNEST('{NULL, "ABC", " "}'::text[]), '^\s $', NULL, 'g')

Expected result: {NULL,"ABC",NULL}

Result: {NULL} <-- Deleted other information

Thank you for all the help in advance.

CodePudding user response：

The function is strict:

select pg_get_functiondef('regexp_replace(text, text, text, text)'::regprocedure);

                              pg_get_functiondef
------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION pg_catalog.regexp_replace(text, text, text, text) 
  RETURNS text                                                                
  LANGUAGE internal                                                           
  IMMUTABLE STRICT                                                            
 AS $function$textregexreplace$function$                                      

(1 row)

According to the documentation:

STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

From a logical point of view, this behavior of the function is fully justified. null is a specific value. It cannot be inserted into a string, added to an integer, compared, etc. As a result you will always get null.

select 'abc' || null, 10  null, 'abc' like null