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

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)

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?)

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.

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\fs1\\bar' -- no workie, although I would have expected it to

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\fs1\\bar' ESCAPE ''  -- works.

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.

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