Search Postgresql Archives

Re: unc paths, like and backslashes on 8.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Chris Curvey wrote:
> 1) Why do I get a warning when doubling a backslash?
> 2) What is the meaning of "E" syntax (E'\\\\fs1\\bar')?
> 3) If I have backslashes in my table, how can I get them back out?
> 4) I'd like to run an update to change the value '\\fs1\bar' to
\\fs1\foo\bar'.  What incantation
> would do that.
> 
> So, trying to figure it out on my own...
> 
> CREATE TABLE FOOBAR
> (  UNC_PATH VARCHAR(100)
> );
> 
> /* first insert attempt */
> INSERT INTO FOOBAR VALUES ('\\FS1\BAR');
> 
> returns a warning:
> 
> WARNING:  nonstandard use of \\ in a string literal
> LINE 1: INSERT INTO FOOBAR VALUES ('\\FS1\BAR');
>                                    ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> Query returned successfully: 1 row affected, 21 ms execution time.
> 
> but the row is inserted.  There is one leading backslash, and the "b"
is some unprintable character.

You have standard_conforming_strings set to "off" and
escape_string_warning set to "on".

So backslash sequences (backslash + something) are not taken literally,
but interpreted as escape sequences.

> Let's try the "E" syntax, whatever that is:
> 
> INSERT INTO FOOBAR VALUES (E'\\FS1\BAR');
> 
> No warning, but exactly the same results again (one leading backslash,
"b" replaced by unprintable
> char).  Let's try E with doubled backslashes:

You are insinuating that the "E" syntax is not well-documented.
Have you tried to read up on it?
What happens here is that you got rid of the warning because
you explicitly said "I'm going to use escape sequences".

> INSERT INTO FOOBAR VALUES (E'\\\\FS1\\BAR');
> 
> okay, that worked.  Yay.   Now let's see if I can get the record back
out with "LIKE":
> 
> SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\FS1%';
> 
> That gets back a record, but the value returned is "\FS1BAR". I'm
missing two backslashes.  I'm too
> confused to even attempt the update.

In LIKE expressions, backslash acts as an escape character,
so you have double escaping: once from the LIKE pattern, and
again from standard_conforming_strings.

You'd have to write:

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\\\\\FS1%';

or say that backslash is not an escape character:

SELECT * FROM FOOBAR WHERE UNC_PATH LIKE E'\\\\FS1%' ESCAPE '';

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux