Re: 8.0.3 regexp_replace()...

On Tue, 31 Jan 2006, rlee0001 wrote:

> I am suggesting that the behaviour of SUBSTRING returning NULL when no
> matches is found is either a bug in PostgreSQL or a flaw in the SQL
> specification. It is not logical.

No, but sadly it seems to be what the SQL spec wants for its similar
construct.

---

In general, SQL's handling of NULLs is badly designed.

Sometimes it's misused (like the substring case).

Sometimes it's confusing (like the IN and NOT IN cases).

Sometimes it's just bizarre. If cardinality of a table expression is
important (ie, count(*) is meaningful), why is DISTINCT defined in a way
that basically does not give results consistent with NULL being unknown.
UNIQUE(q) should return NULL in the presence of NULLs rather than true,
since the real result is well, unknown (the two of these together have the
side effect of UNIQUE being true not guaranteeing that the cardinality of
a subquery and the subquery with distinct being the same).

---