From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: pgsql-sql@postgresql.org
Subject: Re: the use of $$string$$
Date: Fri, 4 Nov 2011 17:12:09 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34.10-0.2-desktop; KDE/4.4.4; i686; ; )
References: <201111040738.29303.johnf@jfcomputer.com>
	<201111040826.16177.johnf@jfcomputer.com>
	<4EB429AD.3030607@archonet.com>
In-Reply-To: <4EB429AD.3030607@archonet.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201111041712.09607.johnf@jfcomputer.com>

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