Thread: regex (not) matching null string

I have found that !~ and !~* do not match the null string even when one
would expect them to.  I am not sure if this is how it is supposed to
work or if this is an error.  If this is how it works, I can't figure
out where it is states in the documentation.

Let me give an example.  Suppose we have a table "t" that look as follows:

# SELECT * FROM t;
 n | s
---+---
 1 | a
 2 | b
 3 |
 4 | d
(4 rows)

Now consider the following query results:

# SELECT * FROM t where s ~* 'a';
 n | s
---+---
 1 | a
(1 row)

This works as expected (by me).

# SELECT * FROM t where s !~* 'a';
 n | s
---+---
 2 | b
 4 | d
(2 rows)

This is not what I would have expected.  Instead, I thought the result
would be:

# SELECT * FROM t where s !~* 'a';  --- This does not happen!!
 n | s
---+---
 2 | b
 3 |
 4 | d
(3 rows)

It seems to me that !~* should just be the inverse of ~*, but this does
not seem to be the case.

David

On Wed, 19 Jun 2002, David M. Kaplan wrote:

> I have found that !~ and !~* do not match the null string even when one
> would expect them to.  I am not sure if this is how it is supposed to
> work or if this is an error.  If this is how it works, I can't figure
> out where it is states in the documentation.

I think it's probably working correctly.  NULL is an unknown value,
you can't necessarily know whether or not it'll match the pattern so
you should get an unknown back in both cases since NOT unknown is
itself unknown.

"David M. Kaplan" <dmkaplan@ucdavis.edu> writes:
> I have found that !~ and !~* do not match the null string even when one
> would expect them to.

NULL strings won't match anything.  I can't tell whether your row 3 is
an empty string or a NULL --- but if it's a NULL, this behavior is
not a bug.  Try "s IS NULL" if you want to find NULLs.

            regards, tom lane