From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Susanne Holzgraefe <miracee@web.de>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: replace() using NULL
Date: Wed, 13 Apr 2022 09:43:04 -0400
Message-ID: <1089632.1649857384@sss.pgh.pa.us> (raw)
In-Reply-To: <Yla1RQYrF9Qn3GR3@jrouhaud>
References: <29A17362-1756-422E-9BD3-2CBE9F8C990F@web.de>
<Yla1RQYrF9Qn3GR3@jrouhaud>
Julien Rouhaud <rjuju123@gmail.com> writes:
> On Wed, Apr 13, 2022 at 10:38:28AM +0200, Susanne Holzgraefe wrote:
>> SELECT REPLACE('xyz‘, 'a‘, NULL); => NULL
> The function is declared as STRICT, which means that it won't be called at all
> and just returns NULL if any of its parameters is NULL.
Right. The SQL standard doesn't seem to have a REPLACE function, so it's
hard to settle this by appealing to the standard; but it's reasonable to
suppose that if they did define it then it would be specified to work like
the existing substring manipulation functions --- and those are all strict
per spec. For example, the spec for SUBSTRING says
d) If at least one of C, S, and L is the null value, then the result of
the <character substring function> is the null value.
and there is equivalent verbiage in every other subparagraph of
SQL:2021 6.32 <string value function>.
>> Tested with Oracle, result is ‚xyz‘.
> Maybe oracle supports such calls as it supports NULL bytes in strings (I
> think), but postgres doesn't.
Oracle is a very suspect reference when it comes to behaviors involving
NULL, because they are not even a little bit spec-compliant there.
My understanding is that they can't really represent a NULL string at
all, and fake it as being an empty string ('') ... which of course
yields that result in this case.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, rjuju123@gmail.com, miracee@web.de, pgsql-bugs@lists.postgresql.org
Subject: Re: replace() using NULL
In-Reply-To: <1089632.1649857384@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox