SELECT with regular pattern '~' delivers wrong output

Fabio Nanni <f.nanni@albourne.com>

From: Fabio Nanni <f.nanni@albourne.com>
To: pgsql-bugs@postgresql.org
Date: 2001-02-08T10:55:48Z
Lists: pgsql-bugs
Hi,
I just found the strange behaviour reported below; I was expecting the
same response from the last two queries...

thanks
Fabio


LONG DESCRIPTION:
A SELECT with regular patterns (~) won't give you the right rows. 

CODE SAMPLE:
select version();
-- OUTPUT:
--                              version                              
---------------------------------------------------------------------
-- PostgreSQL 7.1beta4 on alphaev67-dec-osf4.0f, compiled by cc -std
--(1 row)


create table cities (name text);

insert into cities values ('Alhambra');
insert into cities values ('Jackson');
insert into cities values ('Jacksonville');
insert into cities values ('Zurich');

select * from cities ;
-- OUTPUT:
--     name     
-----------------
-- Alhambra
-- Jackson
-- Jacksonville
-- Zurich
--(4 rows)




select name from cities where name !~ '^A|^B|^C|^[D-I]|^[J][a-c]+kson'
order by name;
-- OUTPUT:
--  name  
----------
-- Zurich
--(1 row)




select name from cities where name !~
'^A|^B|^C|^D|^[E-I]|^[J][a-c]+kson' order by name;
-- OUTPUT:
--     name     
----------------
-- Alhambra
-- Jackson
-- Jacksonville
-- Zurich
--(4 rows)