pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problems with single quotes..
12+ messages / 8 participants
[nested] [flat]

* problems with single quotes..
@ 2001-01-04 18:57  Megalex <megalex@klanomega.com>
  0 siblings, 5 replies; 12+ messages in thread

From: Megalex @ 2001-01-04 18:57 UTC (permalink / raw)
  To: pgsql-sql

ok.. here is my deal..

everytime i try to run an insert and my text contains '\'
it fails..

an sql example would be.

Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
and i get this message...

Error while executing the query; ERROR: parser: parse error at or near
"test"

how can i fix this??

Alex,










^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-01-04 19:50  Megalex <megalex@klanomega.com>
  parent: Horst Herb <hherb@malleenet.net.au>
  0 siblings, 2 replies; 12+ messages in thread

From: Megalex @ 2001-01-04 19:50 UTC (permalink / raw)
  To: pgsql-sql

i already tried escaping it.. but the problem is
with the singlequote-backslash-singlequote combinations..


----- Original Message ----- 
From: "Horst Herb" <hherb@malleenet.net.au>
To: "Megalex" <megalex@klanomega.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, December 02, 2001 1:44 AM
Subject: Re: [SQL] problems with single quotes..


> On Friday 05 January 2001 05:57, Megalex wrote:
> 
> > everytime i try to run an insert and my text contains '\'
> > it fails..
> 
> > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
> > and i get this message...
> 
> > how can i fix this??
> 
> by escaping the '\'
> try
> Insert Into table(userID,FirstName,LastName) values(1,'\\','test')
> ant it will work
> 
> CHeers,
> HOrst




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 07:44  Horst Herb <hherb@malleenet.net.au>
  parent: Megalex <megalex@klanomega.com>
  4 siblings, 1 reply; 12+ messages in thread

From: Horst Herb @ 2001-12-02 07:44 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; +Cc: pgsql-sql

On Friday 05 January 2001 05:57, Megalex wrote:

> everytime i try to run an insert and my text contains '\'
> it fails..

> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
> and i get this message...

> how can i fix this??

by escaping the '\'
try
Insert Into table(userID,FirstName,LastName) values(1,'\\','test')
ant it will work

CHeers,
HOrst



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 07:48  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Megalex <megalex@klanomega.com>
  4 siblings, 0 replies; 12+ messages in thread

From: Stephan Szabo @ 2001-12-02 07:48 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; +Cc: pgsql-sql

On Thu, 4 Jan 2001, Megalex wrote:

> ok.. here is my deal..
>
> everytime i try to run an insert and my text contains '\'
> it fails..
>
> an sql example would be.
>
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
> and i get this message...
>
> Error while executing the query; ERROR: parser: parse error at or near
> "test"
>
> how can i fix this??

You'll need to backslash escape that backslash.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 14:52  Joe Conway <joseph.conway@home.com>
  parent: Megalex <megalex@klanomega.com>
  4 siblings, 0 replies; 12+ messages in thread

From: Joe Conway @ 2001-12-02 14:52 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; +Cc: pgsql-sql

Megalex wrote:

> ok.. here is my deal..
> 
> everytime i try to run an insert and my text contains '\'
> it fails..
> 
> an sql example would be.
> 
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
> and i get this message...
> 
> Error while executing the query; ERROR: parser: parse error at or near
> "test"
> 
> how can i fix this??

It isn't clear to me what literal you're trying to insert, but it looks 
like it's either:

vsreg_192=# select '\'\'\\\'\'';
  ?column?
----------
  ''\''
(1 row)

or

vsreg_192=# select '\'\\\'';
  ?column?
----------
  '\'
(1 row)

HTH,

Joe




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 15:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Megalex <megalex@klanomega.com>
  4 siblings, 1 reply; 12+ messages in thread

From: Tom Lane @ 2001-12-02 15:00 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; +Cc: pgsql-sql

"Megalex" <megalex@klanomega.com> writes:
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')

You're not counting/escaping your quotes correctly.  That literal is
unterminated:

'		opening quote
''		quoted quote (to put a quote in the string)
\'		escaped quote (another way to do the same)
''		quoted quote
oops		you're still inside the literal

I don't know what combination of quotes and/or backslashes you actually
meant to insert, but this command is wrong.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 21:48  postgresql <pgsql@symcom.com>
  parent: Megalex <megalex@klanomega.com>
  4 siblings, 0 replies; 12+ messages in thread

From: postgresql @ 2001-12-02 21:48 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; pgsql-sql


> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
I don't know if you have been able to figure out yet what you need but think of it 
this way:
    'Nick'    is a quoted word. let's form the possesive (nick's)
    'Nick\'s'  notice you still have the outside quotes. The slash is telling the system 
to ignore the single quote that directly follows it.

so what you want is:
INSERT INTO table (userID, FirstName, LastName) VALUES (1, 'william', 'L\'sale')
if you intend to have a single quote in the middle of a string (L'sale)

Ted

-----Original Message-----
From: "Megalex" <megalex@klanomega.com>
To: <pgsql-sql@postgresql.org>
Date: Thu, 4 Jan 2001 12:57:19 -0600
Subject: [SQL] problems with single quotes..

> ok.. here is my deal..
> 
> everytime i try to run an insert and my text contains '\'
> it fails..
> 
> an sql example would be.
> 
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
> and i get this message...
> 
> Error while executing the query; ERROR: parser: parse error at or near
> "test"
> 
> how can i fix this??
> 
> Alex,





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 22:21  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Megalex <megalex@klanomega.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Stephan Szabo @ 2001-12-02 22:21 UTC (permalink / raw)
  To: Megalex <megalex@klanomega.com>; +Cc: pgsql-sql

On Thu, 4 Jan 2001, Megalex wrote:

> i already tried escaping it.. but the problem is
> with the singlequote-backslash-singlequote combinations..

'''\\''' seems to insert a singlequote-backslash-singlquote
for me.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-02 22:25  Roberto Mello <rmello@cc.usu.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 12+ messages in thread

From: Roberto Mello @ 2001-12-02 22:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Megalex <megalex@klanomega.com>; pgsql-sql

On Sun, Dec 02, 2001 at 10:00:22AM -0500, Tom Lane wrote:
> 
> You're not counting/escaping your quotes correctly.  That literal is
> unterminated:
> 
> '		opening quote
> ''		quoted quote (to put a quote in the string)
> \'		escaped quote (another way to do the same)
> ''		quoted quote
> oops		you're still inside the literal

Section 24.5.1.1. Quote Me on That: Escaping Single Quotes

http://www.postgresql.org/idocs/index.php?plpgsql-porting.html

This should help.

-Roberto
-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
       http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
For Sale: Dehydrated H²O - $14 per quart



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-03 03:20  postgresql <pgsql@symcom.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 12+ messages in thread

From: postgresql @ 2001-12-03 03:20 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Megalex <megalex@klanomega.com>; pgsql-sql

but this is exactally correct behavior.....you can use either two single quotes to 
escape the quote or the backslash so.....

 '  ''   \\   ''   '
singlequote to start the sequence(string)
two singlequotes to insert an escape then a singlequote (yields a single quote)
a backslash to escape the following backslash (yields a single backslash)
two singlequotes to insert an escape then a singlequote (yields a single quote)
then ended by a single quote

this is acting correctly.....

Ted
-----Original Message-----
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Megalex <megalex@klanomega.com>
Date: Sun, 2 Dec 2001 14:21:33 -0800 (PST)
Subject: Re: [SQL] problems with single quotes..

> On Thu, 4 Jan 2001, Megalex wrote:
> 
> > i already tried escaping it.. but the problem is
> > with the singlequote-backslash-singlequote combinations..
> 
> '''\\''' seems to insert a singlequote-backslash-singlquote
> for me.
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> 





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2001-12-03 05:23  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: postgresql <pgsql@symcom.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Stephan Szabo @ 2001-12-03 05:23 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; +Cc: Megalex <megalex@klanomega.com>; pgsql-sql

On Sun, 2 Dec 2001, postgresql wrote:

> but this is exactally correct behavior.....you can use either two single quotes to
> escape the quote or the backslash so.....
>
>  '  ''   \\   ''   '
> singlequote to start the sequence(string)
> two singlequotes to insert an escape then a singlequote (yields a single quote)
> a backslash to escape the following backslash (yields a single backslash)
> two singlequotes to insert an escape then a singlequote (yields a single quote)
> then ended by a single quote
>
> this is acting correctly.....

Yes.  I'm assuming that '\' was what he wanted out, so I gave
the sequence that worked for me.  He said he'd tried escaping
the backslash and didn't have it work, but I'm not sure what
he meant by that.




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: problems with single quotes..
@ 2002-01-08 00:21  Luis Miguel Castañeda <lmc@interlink.es>
  parent: Megalex <megalex@klanomega.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Luis Miguel Castañeda @ 2002-01-08 00:21 UTC (permalink / raw)
  To: pgsql-sql

El Thursday 04 January 2001 08:50, Megalex escribió:

> i already tried escaping it.. but the problem is
> with the singlequote-backslash-singlequote combinations..

I use something like this, and works fine for me:

create function quote(text) 
   returns text
   as '
   DECLARE
        quote text;
   BEGIN
        quote := ichar(39); -- quote
        return quote  || $1 || quote;
   END;' 
   language 'plpgsql'; 

-- 
Saludos, lmc@nova.es
_________________________________________________________________
I've lost my faith in nihilism



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2002-01-08 00:21 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-04 18:57 problems with single quotes.. Megalex <megalex@klanomega.com>
2001-12-02 07:44 ` Horst Herb <hherb@malleenet.net.au>
2001-01-04 19:50   ` Megalex <megalex@klanomega.com>
2001-12-02 22:21     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-03 03:20       ` postgresql <pgsql@symcom.com>
2001-12-03 05:23         ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-08 00:21     ` Luis Miguel Castañeda <lmc@interlink.es>
2001-12-02 07:48 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-02 14:52 ` Joe Conway <joseph.conway@home.com>
2001-12-02 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-02 22:25   ` Roberto Mello <rmello@cc.usu.edu>
2001-12-02 21:48 ` postgresql <pgsql@symcom.com>

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