Regexp_replace behavior alters if it is either replacing a string in a string array with a blank string or null

user_5278791 0
Viewed 22

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.

postgresql
1 Answers

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
user_19957380
Related