Quote Question

Hello-
I had a query to insert values into a table where some of the entries
were empty:

INSERT INTO MYTABLE (VALUE_1, VALUE_2)
VALUES ('Hello', '')

which worked like a champ. I discovered some single ticks in the data I
wanted to place in the VALUE_2 field (the data is "Woman's Health") so,
since I'm using Python to drive this, I thought I could change the
single ticks in the above query to double ticks:

INSERT INTO MYTABLE (VALUE_1, VALUE_2)
VALUES ("Hello", "")

but I now get an error complaining about a "zero length delimited
identifier". Rats. So:

1. What's going on above with single and double quotes?

2. How can I insert single (and double) ticks into my data fields?

Thanks for you help,
--greg

--
Greg Lindstrom 501 975.4859 (office)
Senior Programmer 501 219-4455 (fax)
NovaSys Health greg(dot)lindstrom(at)novasyshealth(dot)com
Little Rock, Arkansas

"We are the music makers, and we are the dreamers of dreams." W.W.

Confidentiality Notice
----------------------
This email and any attachments to it are privileged and confidential and are intended solely for use of the individual or entity to which they are addressed. If the reader of this message is not the intended recipient, any use, distribution, or copying of this communication, or disclosure of all or any part of its content to any other person, is strictly prohibited. If you have received this communication in error, please notify the sender by replying to this message and destroy this message and delete any copies held in your electronic files. Thank you.

On Mar 30, 2005, at 10:31 AM, Greg Lindstrom wrote:

> INSERT INTO MYTABLE (VALUE_1, VALUE_2)
> VALUES ("Hello", "")
>
> but I now get an error complaining about a "zero length delimited
> identifier". Rats. So:
>
> 1. What's going on above with single and double quotes?

In PostgreSQL, double quotes are only used to quote identifiers such as
tables and columns. You only need to do this if you want to include
non-standard characters in the identifier name (e.g. spaces) or need to
preserve case. For example,

INSERT INTO "My Table" ...

>
> 2. How can I insert single (and double) ticks into my data fields?
>

You double the quote or use \

VALUES('Woman''s Health', '') or
VALUES('Woman\'s Health', '')

There should be a function in your pg Python interface to handle this
for you.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Wed, Mar 30, 2005 at 11:22:20AM -0500, John DeSoi wrote:
> On Mar 30, 2005, at 10:31 AM, Greg Lindstrom wrote:
> >
> >2. How can I insert single (and double) ticks into my data fields?
>
> You double the quote or use \
>
> VALUES('Woman''s Health', '') or
> VALUES('Woman\'s Health', '')
>
> There should be a function in your pg Python interface to handle this
> for you.

Indeed, and if you use parameterized queries then it should happen
automagically. Is this client code or a server-side (PL/Python)
function? If client-side, which PostgreSQL driver are you using?

conn = psycopg.connect('dbname=testdb')
curs = conn.cursor()
sql = 'INSERT INTO foo (val1, val2) VALUES (%s, %s)'
val1 = "single'quote"
val2 = 'double"quote'
curs.execute(sql, (val1, val2))
conn.commit()

SELECT * FROM foo;
id | val1 | val2
----+--------------+--------------
1 | single'quote | double"quote
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/