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