Postgres Pro
Downloads
Home   >   mailing lists

Thread: ' escape

' escape

From
Fabio Mancinelli
Date:
Hello all

I have a question. When I create a table I cannot use any escape to put a
' character in the default field definition.

CREATE TABLE Test (name varchar(128) default 'Something with '' into');

Fails. I've tried also other alternatives :
default "Something with ' into"
default "Something with '' into"
default 'Something with \' into'
default 'Something with \27 into'
default 'Something with \0x27 into'

Everything fails.

How can I set a default with a string containing a ' ?

Thank you all.

Bye

----------------------------------------------------------------------------
 Greetings from Fabio Mancinelli   E-Mail : mancinel@univaq.it
 Computer Science Student                   xenon@olografix.org
 University of L'Aquila               WWW : http://univaq.it/~mancinel
 (PGP Public Key available)                 http://www.olografix.org/xenon
 A.L.U. Member

Re: [SQL] ' escape

From
Herouth Maoz
Date:
At 17:31 +0200 on 7/12/98, Fabio Mancinelli wrote:


> I have a question. When I create a table I cannot use any escape to put a
> ' character in the default field definition.
>
> CREATE TABLE Test (name varchar(128) default 'Something with '' into');
>
> Fails. I've tried also other alternatives :
> default "Something with ' into"
> default "Something with '' into"
> default 'Something with \' into'
> default 'Something with \27 into'
> default 'Something with \0x27 into'
>
> Everything fails.

Interesting bug.

As a workaround, if you really need that default value, define a simple SQL
function which returns it:

testing=> CREATE FUNCTION specialValue() RETURNS varchar
testing-> AS 'SELECT ''a value with '''' in it''::varchar'
testing-> LANGUAGE 'sql';
CREATE
testing=> SELECT specialValue();
specialvalue
--------------------
a value with ' in it
(1 row)

Now define your table. Here is an example:

testing=> CREATE TABLE test6 (
testing->   id int,
testing->   name varchar(128) DEFAULT specialValue()
testing-> );
CREATE
testing=> INSERT INTO test6 (id) values (1);
INSERT 932640 1
testing=> SELECT * FROM test6;
id|name
--+--------------------
 1|a value with ' in it
(1 row)

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [SQL] ' escape

From
jwieck@debis.com (Jan Wieck)
Date:
> > CREATE TABLE Test (name varchar(128) default 'Something with '' into');
> >
> > Fails. I've tried also other alternatives :
> > default "Something with ' into"
> > default "Something with '' into"
> > default 'Something with \' into'
> > default 'Something with \27 into'
> > default 'Something with \0x27 into'
> >
> > Everything fails.
>
> Interesting bug.
>
> As a workaround, if you really need that default value, define a simple SQL
> function which returns it:

    Hmmm - bug or feature - dunno. But telling

        ... DEFAULT 'Something with '''' into' ...

    works pretty good and as I expected it, results in one single
    quoute.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Re: [SQL] ' escape

From
Bruce Momjian
Date:
> > > CREATE TABLE Test (name varchar(128) default 'Something with '' into');
> > >
> > > Fails. I've tried also other alternatives :
> > > default "Something with ' into"
> > > default "Something with '' into"
> > > default 'Something with \' into'
> > > default 'Something with \27 into'
> > > default 'Something with \0x27 into'
> > >
> > > Everything fails.
> >
> > Interesting bug.
> >
> > As a workaround, if you really need that default value, define a simple SQL
> > function which returns it:
>
>     Hmmm - bug or feature - dunno. But telling
>
>         ... DEFAULT 'Something with '''' into' ...
>
>     works pretty good and as I expected it, results in one single
>     quoute.

I see the problem here:

test=> create table test33 (a int,x char(10) default 'x\'y');
ERROR:  parser: parse error at or near "y"


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [SQL] ' escape

From
jwieck@debis.com (Jan Wieck)
Date:
> >     Hmmm - bug or feature - dunno. But telling
> >
> >         ... DEFAULT 'Something with '''' into' ...
> >
> >     works pretty good and as I expected it, results in one single
> >     quoute.
>
> I see the problem here:
>
> test=> create table test33 (a int,x char(10) default 'x\'y');
> ERROR:  parser: parse error at or near "y"

    But

    create table ... default 'x\'\'y');

    does. Whenever something around quotes doesn't work, I first
    try to double them.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Re: [SQL] ' escape

From
Herouth Maoz
Date:
At 11:27 +0200 on 8/12/98, Jan Wieck wrote:


>
>     But
>
>     create table ... default 'x\'\'y');
>
>     does. Whenever something around quotes doesn't work, I first
>     try to double them.

Nevertheless, it is a bug. You should double your quotes when they are
inside other quotes. When surrounded by doubled quotes, you have to
quadruple them. Or to escape them and escape the escapes. The following
also works:

testing=> CREATE TABLE test1
testing-> (
testing->    id int,
testing->    name varchar(128) default 'something with \\'' in it'
testing-> );

But it shouldn't, because it is only surrounded by single unescaped quotes.
If this string is used in any other context, it will have a single slash
and a single quote in it. Something is amiss.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [SQL] ' escape

From
Bruce Momjian
Date:
>
> Hello all
>
> I have a question. When I create a table I cannot use any escape to put a
> ' character in the default field definition.
>
> CREATE TABLE Test (name varchar(128) default 'Something with '' into');
>
> Fails. I've tried also other alternatives :
> default "Something with ' into"
> default "Something with '' into"
> default 'Something with \' into'
> default 'Something with \27 into'
> default 'Something with \0x27 into'
>
> Everything fails.
>
> How can I set a default with a string containing a ' ?
>
> Thank you all.

Added to TODO:

    * DEFAULT handles single quotes in value by requiring too many quotes



--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026