Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.9.1/8.9.1) with ESMTP id LAA20600 for ; Mon, 7 Dec 1998 11:04:44 -0500 (EST) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.8/8.8.8) with ESMTP id SAA24478; Mon, 7 Dec 1998 18:02:54 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 7 Dec 1998 18:04:19 +0200 To: Fabio Mancinelli From: Herouth Maoz Subject: Re: [SQL] ' escape Cc: pgsql-sql@postgreSQL.org 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