Escaping special characters

About the string "Smith \& Jones".

According to the documentation,

INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');

must work. But it doesn't. So, double the enclosed quotes:

INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');

Doesn't.

It works fine, but with a warning, as

INSERT INTO thing (name) VALUES ('Smith \\& Jones');

But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
So 40,000 years from now I'll be on 8.2.3.

Granted, I'm not very bright. Would appreciate your help.

--Elle

Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't.

I think you'll find the documentation says to use:
SELECT E'Smith \\& Jones';

Note that the "E" precedes the quoted string, it isn't embedded in it.
If there's an example in the docs that looks like yours, that's a bug.

> But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
> So 40,000 years from now I'll be on 8.2.3.

Doubtful - you're missing 9 releases of bugfixes already. Probably find
all your data gets eaten by a bug long before then. Read the release
notes for 8.2.x and upgrade to 8.2.<latest> at your earliest convenience.

--
Richard Huxton
Archonet Ltd

>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't. So, double the enclosed quotes:
>
> INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');
>
> Doesn't.
>
> It works fine, but with a warning, as
>
> INSERT INTO thing (name) VALUES ('Smith \\& Jones');
>
> But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
> So 40,000 years from now I'll be on 8.2.3.
>

I could be wrong, but shouldn't it be:

INSERT INTO thing (name) VALUES ('Smith E'\\& Jones');

I'm not sure why you're including an extra single or double-quote in the
string.

Regards

Thom

On Tue, Mar 17, 2009 at 10:35:20PM +1030, Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't.

You're putting things in the wrong places! The "E" says that the
following literal is using C style escaping. I.e. you want to say:

E'Smith \\& Jones'

Hope that helps!

--
Sam http://samason.me.uk/

2009/3/17 Thom Brown <thombrown(at)gmail(dot)com>

>
> I could be wrong, but shouldn't it be:
>
> INSERT INTO thing (name) VALUES ('Smith E'\\& Jones');
>
> I'm not sure why you're including an extra single or double-quote in the
> string.
>
> Regards
>
> Thom
>

Sorry, (damn copy & paste). I meant:

INSERT INTO thing (name) VALUES (E'Smith \\& Jones');

Thom

Neanderthelle Jones wrote:

> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't. So, double the enclosed quotes:
>
> INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');

The E can't be inside the string, it must appear before the quote
starting the string.

But first, you need to choose a setting for
standard_conforming_strings, especially if you're concerned with
compatibility against future versions. Either your session has
standard_conforming_strings set to ON or set to OFF. This is what
defines which characters have to be quoted and how.

if OFF you must escape the backslash:
test=> set standard_conforming_strings=off;
SET
test=> select E'Smith \\& Jones';
?column?
----------------
Smith \& Jones
(1 row)

if ON you don't:
test=> set standard_conforming_strings=on;
SET
test=> select 'Smith \& Jones';
?column?
----------------
Smith \& Jones
(1 row)

ON is supposed to become the default at some point in the future.

Cordialement,
--
Daniel

On Tue, 17 Mar 2009, Sam Mason wrote:

> You're putting things in the wrong places! The "E" says that the
> following literal is using C style escaping. I.e. you want to say:
>
> E'Smith \\& Jones'

Thanks. Now I understand.

Elle.