From: Richard Huxton <dev@archonet.com>
To: John Fabiani <johnf@jfcomputer.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: the use of $$string$$
Date: Fri, 04 Nov 2011 18:06:37 +0000
Message-ID: <4EB429AD.3030607@archonet.com> (raw)
In-Reply-To: <201111040826.16177.johnf@jfcomputer.com>
References: <201111040738.29303.johnf@jfcomputer.com>
<201111040826.16177.johnf@jfcomputer.com>
On 04/11/11 15:26, John Fabiani wrote:
> On Friday, November 04, 2011 07:38:29 am John Fabiani wrote:
>> Hi,
>> I just discovered that I can use $$string$$ to account for the problem of
>> single quotes in the string (or other strange char's). However, I noticed
>> that the table field contained E'string'. I actually tried to find info on
>> this but I did not find anything.
>>
>> Could someone explain what it means or better provide a web link for me to
>> discover the info.
> Thank you both for the quick replies and the links.
>
> What I still do not understand (I'm a little slow) is the fact that pgadmin3
> showed the data as E'string'. So I'm wondering if the data is now different
> in the sense that I need use the 'E' in the field.
I think you're missing some bit of the puzzle here.
There's the universal problem of how to deal with quote marks inside
quotes. The SQL standard says you double the quote.
SELECT length('ab''de');
length
--------
5
Traditionally, PostgreSQL let you use backslash escapes too, not just
for single quotes but for some other common C-style idioms.
SELECT length(E'ab\'de');
length
--------
5
For the last few years, this has been moved into its own quoting style
so standard strings are always well, standard.
SELECT length('ab\nde');
length
--------
6
SELECT length(E'ab\nde');
length
--------
5
The [E'] is an opening quote - both characters. It isn't part of the
value at all. If a field contains "E'" then you put it there, possibly
by quoting something in pgadmin that was already quoted.
Likewise you can use $$..$$ to quote strings (actually $$whatever$$).
That gives you sql-standard escaping apart from single quotes. It's
especially useful for function bodies which tend to contain their own
string literals.
SELECT length($$ab\nde$$);
length
--------
6
None of this should matter from an application however, since its
database access library should do all the quoting for you.
HTH
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, johnf@jfcomputer.com
Subject: Re: the use of $$string$$
In-Reply-To: <4EB429AD.3030607@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox