SEO

#1 (**permalink**) 04-09-2008, 11:20 PM

I have data that I'm running through pg_escape_sting in php and then adding
to stdin for a copy command. The problem is "O'reilly" is being changed to
"O''Reilly" in the string and then in the db. I saw with the copy command I
can specify the escape but it isn't working for me. Should this command fix
this double 'single' quote issue when I put it in the db? And what is the
proper syntax?

COPY mytable FROM stdin with escape

Thanks!

#2 (**permalink**) 04-09-2008, 11:20 PM

"blackwater dev" <blackwaterdev@gmail.com> writes:
> I have data that I'm running through pg_escape_sting in php and then adding
> to stdin for a copy command. The problem is "O'reilly" is being changed to
> "O''Reilly" in the string and then in the db.

pg_escape_string is designed to produce a string properly quoted for use
as a literal in a SQL command. It is completely wrong for data that is
to go into COPY input. I kinda doubt that PHP has anything built-in
that's suitable for COPY, though I could be wrong ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#3 (**permalink**) 04-09-2008, 11:20 PM

blackwater dev wrote:
> I have data that I'm running through pg_escape_sting in php and then adding
> to stdin for a copy command. The problem is "O'reilly" is being changed to
> "O''Reilly" in the string and then in the db. I saw with the copy command I
> can specify the escape but it isn't working for me. Should this command fix
> this double 'single' quote issue when I put it in the db? And what is the
> proper syntax?
>
> COPY mytable FROM stdin with escape
>
>

COPY mytable (...) FROM STDIN WITH CSV ESCAPE "'";

http://www.postgresql.org/docs/8.3/static/sql-copy.html

But CSV comes with a lot of baggage. You'd be far better off doing
tab-delimited, unquoted fields (if you have no tabs in your data).

COPY mytable (...) FROM STDIN;
....
\.

I can't remember precisely all of what pg_escape_string() does, but if
you need it for something else you could always do this afterward ;-)

implode("\t", str_replace("''", "'", $row))

b

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

copy with escape