Thread: copy with escape

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!

"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

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