Hi all,
I'm sure this has been discussed before, but I am not too sure what
key search-terms to use to find any potentially relevant discussions.
Issue: I have two tables, each has a column that contains a directory
path. First table contains a starting path and the second holds
sub-paths (retaining full path names from root directory). In short,
first table entries are sub-strings of those found in the second
table.
I need to match entries in second table to the first, so I use the
following in my WHERE clause:
... WHERE second.path LIKE first.path||'%'
This seemed to work at first, but it fails if the paths use
back-slashes (like Windows paths).
Following is a simple test-case to illustrate what I described.
PostgreSQL 9.1.1 (similar results with much older version)
$ psql -d db -e < testcase.sql
CREATE TEMPORARY TABLE foo (id INTEGER, a TEXT);
CREATE TABLE
CREATE TEMPORARY TABLE bar (id INTEGER, b TEXT);
CREATE TABLE
INSERT INTO foo VALUES (0, '/root/a/b');
INSERT 8030228 1
INSERT INTO foo VALUES (1, '\root\a\b');
INSERT 8030229 1
INSERT INTO bar VALUES (0, '/root/a/b/c/*nix');
INSERT 8030230 1
INSERT INTO bar VALUES (1, '\root\a\b\c\Windows');
INSERT 8030231 1
SELECT * FROM foo;
id | a
----+-----------
0 | /root/a/b
1 | \root\a\b
(2 rows)
SELECT * FROM bar;
id | b
----+---------------------
0 | /root/a/b/c/*nix
1 | \root\a\b\c\Windows
(2 rows)
SELECT a,b, b LIKE a||'%' FROM foo JOIN bar USING (id);
a | b | ?column?
-----------+---------------------+----------
/root/a/b | /root/a/b/c/*nix | t
\root\a\b | \root\a\b\c\Windows | f
(2 rows)
Hmm... just tried these two cases as well which seem interesting:
SELECT '\root\a\b\c\Windows' LIKE '\root\a\b'||'%';
?column?
----------
f
(1 row)
mod=# SELECT '\root\a\b\c\Windows' LIKE '\\root\\a\\b'||'%';
?column?
----------
t
(1 row)
Is this a bug in the SQL statement, or a bug in PostgreSQL? If the
former, what is the correct way to do this? If the latter, is there a
work-around?
I realize the same thing can be done with the following statement, but
it is harder to read and might be slightly more expensive to run on a
large data set.
SELECT a,b,substr(b,1,length(a)), substr(b,1,length(a)) = a FROM foo
JOIN bar USING (id);
a | b | substr | ?column?
-----------+---------------------+-----------+----------
/root/a/b | /root/a/b/c/*nix | /root/a/b | t
\root\a\b | \root\a\b\c\Windows | \root\a\b | t
(2 rows)
Thanks,
--patrick
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
