Re: the use of $$string$$

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