Re: unc paths, like and backslashes on 8.4 - Mailing list pgsql-general

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Curvey
Sent: Thursday, September 27, 2012 1:44 PM
To: pgsql
Subject: Re: [GENERAL] unc paths, like and backslashes on 8.4

Many thanks to David and Albe for their kind assistance.  I've looked at the docs and run some experiments, and this is what I found.  I'm going to answer my own questions in a slightly different order, because it seems to make the explanation more logical.  Note that the rules appear to be different for LIKE clauses. (more on that later)

2) What is the meaning of "E" syntax (E'\\\\fs1\\bar')?

Example:  E'eat\tat\njoes' is interpreted as "eat<tab>at<newline>joes"

because STANDARD_CONFORMING_STRINGS is off and ESCAPE_STRING_WARNING is on (thanks Albe).  Turning on STANDARD_CONFORMING_STRINGS makes a literal backslash "just another character" (unless using E-syntax).  Turning off STANDARD_CONFORMING_STRINGS is the equivalent of using E-syntax for each literal string.  (Are those statements true?)

>>>>> Yes, they are.

ESCAPE_STRING_WARNING is there to notify you if you are writing code that may behave differently in the future.  The warning is just a warning, the statement will go through.

Example:  

'eat\tat\njoes' is interpreted as "eat<tab>at<newline>joes" STANDARD_CONFORMING_STRINGS is off

'eat\tat\njoes' is interpreted as "eat[backslash][tee]at[backslash][en]joes" STANDARD_CONFORMING_STRINGS is on

By changing to the 'E' syntax (E'eat\tat\njoes'), you are specifically saying that you want the <tab> and <newline> characters, regardless of how STANDARD_CONFORMING_STRINGS is set.    (I guess there is a logical implication here that if STANDARD_CONFORMING_STRINGS is off, then the only way to get a tab into a field would be to insert a ctrl-I somehow.)

added bonus information that will be obvious to PG masters, but I had to find it: you can find the server setting with "SHOW STANDARD_CONFORMING_STRINGS" and you can set it for your session with "SET STANDARD_CONFORMING_STRINGS=ON".

I'm not sure how I got myself into that situation.

SET STANDARD_CONFORMING_STRINGS=ON;

UPDATE FOOBAR

SET UNC_PATH = REPLACE('\bar','\foo\bar')

WHERE UNC_PATH LIKE E'\\\\fs1\bar%' ESCAPE '';

====================================

I've tested the above rules for SELECT, INSERT, and UPDATE, and the rules seem to hold.  They also hold for WHERE clauses when searching for equality.  But they don't seem to hold for LIKE.

SELECT * FROM FOOBAR WHERE UNC_PATH = '\\fs1\bar' -- works

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE '\\fs1\bar' -- no workie, although I would have expected it to. [Like escapes the first pair]

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\fs1\\bar' -- no workie, although I would have expected it to [literal escape, then like escape]

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\fs1\\bar' ESCAPE ''  -- works. [literal escape, not Like escape]

So I guess the rules for string interpretation of backslashes in LIKE are just different.  

A) The backslash always escapes another backslash, regardless of what the ESCAPE clause is. In fact, it appears that you HAVE to specify some other escape clause to get it to work 

B) You can't use another character to escape a backslash.

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'@\@\fs1%' ESCAPE '@' -- does not work.

>>>>>> If you change the LIKE escape character the “\” no longer has any special meaning.  But to get your new escape character you double-it up as well.  LIKE ‘@@’ matches a single “@” in the example above

>>>>>> In your example you get E‘@\@\fs1%’ -> E’@{\@}{\f}s1% -> ‘@[formfeed]s1%’ -> no-idea what ‘{@[formfeed]}s1%’ resolves to.  The literal escaping always occurs first, then the LIKE escaping. Note that the “{}” denotes a single expression seen by the literal parser.

I think I got it now.  LIKE-with-backslash is just different.  This has been a learning experience!