Re: the use of $$string$$

On Friday, November 04, 2011 11:06:37 am Richard Huxton wrote:
> 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

thanks for the reply.

I'm using psycopg2.

This is what I'm doing from python
myvarString = "long string that contains single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, $$%s$$)",(123,
myvarString))

When I execute the above I'm seeing:
E'long string that contains single quotes' in the field. When I do a "select
* from table" I get E'long string that contains single quotes'.

If I do

myvarString = "long string that without single quotes"
cusor.execute("insert into table (pkid, myfield) values (%s, %s)",(123,
myvarString))

I get the following:
"long string that without single quotes"

I have to be doing something wrong. But where?

Johnf