Thread: the use of $$string$$
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. Thanks in advance, Johnf
On Fri, Nov 4, 2011 at 7:38 AM, John Fabiani <johnf@jfcomputer.com> 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. E'...' is the escape string syntax. You can find examples of it in this page (among others) http://www.postgresql.org/docs/9.0/interactive/functions-matching.html You might want to read up on standard_conforming_strings and escape_string_warning too, as they influence how the escaping works.
John Fabiani <johnf@jfcomputer.com> writes: > 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. http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html See "4.1.2.4. Dollar-quoted String Constants", about halfway down the page. You might care to read the rest of 4.1.2 while at it. regards, tom lane
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. > > Thanks in advance, > Johnf 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. For example I have the last name of O'Reilly the field lname now appears to contain E'O'Reilly' So will the normal select statements work; Select * from table where lname = 'O'' Reilly' Select * from table where lname like 'O%' or will I need to know the data contains the "E" and add the 'E' to the sql statements? Thanks Johnf
On Nov 4, 2011, at 11:26, John Fabiani <johnf@jfcomputer.com> 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. >> >> Thanks in advance, >> Johnf > > > 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. > > For example I have the last name of O'Reilly > the field lname now appears to contain E'O'Reilly' > > So will the normal select statements work; > > Select * from table where lname = 'O'' Reilly' > Select * from table where lname like 'O%' > > or will I need to know the data contains the "E" and add the 'E' to the sql > statements? > > Thanks > Johnf > > Try it and see. If, indeed, the E is part of the data you should fix your process. Done correctly there should be no difference in the endresult regardless of how you choose to identify your strings. Don't use pgadmin3 myself so I don't know if what you are looking at would include the E. If it is outputting DDL (I.e.,INSERT statements) it might but simple SELECT results should not. David J.
On Friday, November 04, 2011 09:05:19 am David Johnston wrote: > On Nov 4, 2011, at 11:26, John Fabiani <johnf@jfcomputer.com> 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. > >> > >> Thanks in advance, > >> Johnf > > > > 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. > > > > For example I have the last name of O'Reilly > > the field lname now appears to contain E'O'Reilly' > > > > So will the normal select statements work; > > > > Select * from table where lname = 'O'' Reilly' > > Select * from table where lname like 'O%' > > > > or will I need to know the data contains the "E" and add the 'E' to the > > sql statements? > > > > Thanks > > Johnf > > Try it and see. > > If, indeed, the E is part of the data you should fix your process. Done > correctly there should be no difference in the end result regardless of > how you choose to identify your strings. > > Don't use pgadmin3 myself so I don't know if what you are looking at would > include the E. If it is outputting DDL (I.e., INSERT statements) it might > but simple SELECT results should not. > > David J. I am testing - but I lack the data to do so. When I discovered the 'E' I quickly decided against using the $$ to insert data. But I did use it with one field and when I do a select from python I do get the 'E' on the only field I have. That was disappointing. I was hoping to short cut the testing and get good advise from the group. I really need to look into this much further but I also have to get the job done. Johnf
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
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
On 05/11/11 00:12, John Fabiani wrote:
> I'm using psycopg2.
OK - bear in mind, I'm not a Python user.
> 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'.
OK, so it seems psycopg is quoting your strings for you (as you'd
expect). It's presumably turning your query into: ... values (E'123', $$E'<long string>'$$)
So - the $$ quoting is unnecessary here - just use the % placeholders.
Incidentally, should it be %s for the numeric argument?
> 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"
That seems sensible to me (unless there's a typo in your example). You
shouldn't need to quote any of your values in your Python code - it's
doing it for you. I'm guessing there are other options beside %s for
other data-types (integers,floats,boolean etc).
-- Richard Huxton Archonet Ltd
On mån, 2011-11-07 at 08:44 +0000, Richard Huxton wrote:
> > 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'.
>
> OK, so it seems psycopg is quoting your strings for you (as you'd
> expect). It's presumably turning your query into:
> ... values (E'123', $$E'<long string>'$$)
> So - the $$ quoting is unnecessary here - just use the % placeholders.
>
> Incidentally, should it be %s for the numeric argument?
Yes.
On 2011-11-07, Richard Huxton <dev@archonet.com> wrote: > On 05/11/11 00:12, John Fabiani wrote: > OK, so it seems psycopg is quoting your strings for you (as you'd > expect). It's presumably turning your query into: > ... values (E'123', $$E'<long string>'$$) > So - the $$ quoting is unnecessary here - just use the % placeholders. > > Incidentally, should it be %s for the numeric argument? psycopg2 seems to only accept %s as a place-holder, it's not printf it just looks a bit like it. -- ⚂⚃ 100% natural