Re: unc paths, like and backslashes on 8.4

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)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')?

the "E" syntax allows you to enter "special" characters into a field (\n =
newline, \t = tab, \b = backspace, etc). A double-backslash is interpreted
as a backslash. Any character besides backslash and
b,f,n,r,t,x,0,1,2,3,4,5,6,7,8,9 is taken literally and the backslash is
ignored. (See table 4-1 in the docs for details on what each character
means).

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

1) Why do I get a warning when doubling a backslash?

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".

3) If I have backslashes in my table, how can I get them back out?

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

4) I'd like to run an update to change the value '\\fs1\bar' to
\\fs1\foo\bar <file:///\\fs1\foo\bar> '. What incantation would do that.

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'@\(at)\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'@\(at)\fs1%' -> E'@{\(at)}{\f}s1% ->
'@[formfeed]s1%' -> no-idea what '{(at)[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!