Re: MS Access to PostgreSQL

On Fri, Mar 11, 2005 at 05:19:35PM +0000, William Shatner wrote:
> I have recently migrated from MS Access to PostgreSQL.Previously I had
> a SQL command
>
> ResultSet aGroupResultSet = aGroupPathStmt.executeQuery(
> "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE
> \"fullpath\" Like '" +
> aPath + "'");
>
> where aPath was equal to 'folder\another folder\%'.
>
> The field to be edited stores the full path in the format
> 'folder\folder1\folder2' and so on...
> The purpose being to change all groups at this level of the
> hieracarchy and below, this was achieved using the '%' in Access, this
> however doesn't seem to work in PostgreSQL, it doesn't error out but
> it just seems to see the '%' as a normal character.

You're running into problems with \ being the escape character in
string constants and again in patterns. There's some discussion
of this in the "Pattern Matching" section of the "Functions and
Operators" chapter in the documentation:

http://www.postgresql.org/docs/8.0/interactive/functions-matching.html

Here are some ways to make it work (dollar quoting available only
in 8.0 and later):

fullpath LIKE 'folder\\\\another folder\\\\%'
fullpath LIKE 'folder\\another folder\\%' ESCAPE ''
fullpath LIKE $$folder\\another folder\\%$$
fullpath LIKE $$folder\another folder\%$$ ESCAPE ''

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/