From: | "Hardy, Paul" <Paul(dot)Hardy(at)cra-arc(dot)gc(dot)ca> |
---|---|
To: | "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | pg_bulkloader |
Date: | 2012-01-13 15:07:16 |
Message-ID: | FEA7F42821250E4492218521FD194BC81FFDFC2106@SD01CFMV0011.PROD.NET |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have no problem loading a table using bulkloader. Eg.
OUTPUT = schema.tablename # [<schema_name>.]table_name
INPUT = /filelocation/table.csv # Input data location (absolute path)
TYPE = CSV # Input file type
QUOTE = "\"" # Quoting character
ESCAPE = \ # Escape character for Quoting
DELIMITER = ~
However, we have two problems I cannot seem to overcome or get the correct setup... that is, our schema names contain a # sign in them therefore if you want to reference this you need double quotes.
"schema#1".tablename
I cannot seem to get the ctl file to accept this name.
The other problem is we use hex 7F as our delemeter for all our files and I cannot seem to overcome that problem as well.
Note that I can load this file no problem with \copy command, but we would like to test the difference between the pg_loader and \copy for large volume files.
Eg. Of \copy command....
#!/bin/ksh
. /source statement
hex7f="E'\x7F'"
schema='"schema#1"'
table="tablename"
database="db"
starttime=$(psql -d $database -tc "select now();")
echo $starttime
psql -c "truncate $schema.$table;" -d $database
psql -c "\copy $schema.$table FROM /filelocation/$table.csv DELIMITER AS $hex7f NULL AS ''" -d $database
this will successfully load a table with schema name of # in it with hex 7F as the delimeter.
Can anyone out there help?
Paul Hardy
e-mail: Paul(dot)Hardy(at)cra-arc(dot)gc(dot)ca
From | Date | Subject | |
---|---|---|---|
Next Message | Cefull Lo | 2012-01-13 15:59:56 | time zone problem |
Previous Message | David Johnston | 2012-01-13 14:02:52 | Re: Appending a newline to a column value - in a psql cronjob |