someone please explain this regex behaviour
Hi,
consider the following simple example, where i want to select a path by
using a where clause with the case insensitive operator "~*". Everything is
ok where i get a result of one row, but i don't understand the results with
0 rows:
backup=> select version();
version
-------------------------------------------------------------
PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
backup=> create table foo (path varchar(300));
CREATE
backup=> insert into foo (path) values ('/My');
INSERT 29400 1
backup=> select path from foo where path ~* '/My';
path
----
/My
(1 row)
backup=> select path from foo where path ~* '^/My';
path
----
/My
(1 row)
backup=> select path from foo where path ~* '^/my';
path
----
(0 rows)
backup=> select path from foo where path ~* '/my';
path
----
/My
(1 row)
backup=> select path from foo where path ~* '/mY';
path
----
/My
(1 row)
backup=> select path from foo where path ~* '^/mY';
path
----
(0 rows)
Why e.g. does the statement "select path from foo where path ~* '^/my';"
not return the only entry "/My"? Can someone explain this?
--
Regards, martin@unix-ag.org
Martin Leja <Martin.Leja@unix-ag.uni-siegen.de> writes:
Why e.g. does the statement "select path from foo where path ~* '^/my';"
not return the only entry "/My"? Can someone explain this?
I think you're getting bitten by the LIKE-index-optimization-in-non-
ASCII-locale problem. Are you running the server in a locale other
than "C"? See the many past threads about this type of issue ...
regards, tom lane
At 18:47 27.01.2001 -0500, Tom Lane wrote:
Martin Leja <Martin.Leja@unix-ag.uni-siegen.de> writes:
Why e.g. does the statement "select path from foo where path ~* '^/my';"
not return the only entry "/My"? Can someone explain this?I think you're getting bitten by the LIKE-index-optimization-in-non-
ASCII-locale problem. Are you running the server in a locale other
than "C"? See the many past threads about this type of issue ...
i'm not quite familiar with this locale stuff, so i searched the docs and
found the following in doc/postgresql-doc/postgres/install12893.htm:
...
If you configure and compile Postgres with --enable-locale then you should
set the locale environment to "C" (or unset all "LC_*" variables) by
putting these additional lines to your login environment before starting
postmaster:
LC_COLLATE=C
LC_CTYPE=C
export LC_COLLATE LC_CTYPE
...
i then changed /etc/init.d/postgresql (postmaster start script in debian)
accordingly, restarted postmaster with the script, but unfortunetly i get
the same results.
I wonder if i disabled the LIKE-index-optimization-in-non-ASCII-locale with
the above action at all and if this is the problem of my select results.
Isn't there a "psql -c 'show ???'" command which can report the locale
setting to me?
--
Regards, martin@unix-ag.org