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.Hardy@cra-arc.gc.ca
