Re: PQescapeStringConn

On Jul 30, 2010, at 9:11 AM, Richard Huxton wrote:

> On 30/07/10 16:57, Scott Frankel wrote:
>>
>> On Jul 30, 2010, at 1:13 AM, Richard Huxton wrote:
>>
>>> On 30/07/10 07:52, Scott Frankel wrote:
>>>> I have a number of very long strings that each contain many
>>>> instances of
>>>> semi-colons, single quotes, forward and back slashes, etc. I'm
>>>> looking
>>>> for an efficient and safe way to write them to my db using a
>>>> prepared
>>>> statement.
>
> You're going to have to pre-process the strings in some way, or
> there will always be the chance of problems. Probably the best way
> to handle a bulk insert is through the COPY command:

Excellent! Thanks for the lead. I see from the docs:
COPY weather FROM '/home/user/weather.txt';

I am using Python to create the strings; and Python is the ultimate
consumer of the strings after storage in the db. Thus I have a fair
degree of control over the strings' formatting. COPY from a plain
text file on my server looks very promising.

Thanks!
Scott

> BEGIN;
>
> COPY foo (name, body) FROM stdin;
> n1 b1
> n2 b2
> foo this will fail 'fer sher;' on the characters inside the string
> \.
>
> COMMIT;
>
> By default COPY expects one line per row, with columns separated by
> tab characters. You can also have '/path/to/file/name' instead of
> stdin, but the file will need to be accessible from the backend
> process. If that's not the case (and it probably isn't) then you
> want to use psql's "\copy" variant which views the world from the
> client end of things.
>
> COPY is faster than separate inserts and the only characters you
> need to worry about are tab, carriage-return and newline. These
> would be replaced by the sequences "\t", "\r", "\n".
>
> I don't know what format your strings are in initially, but a bit of
> perl/python/ruby can easily tidy them up.
>
> Finally, more recent versions of PG have a COPY that supports CSV
> formatting too. See the manuals for more details on this.
>
> --
> Richard Huxton
> Archonet Ltd
>