SCM

[#1011149] Hex and Quote characters allowed in control file?

View Trackers | Support Requests | Download .csv | Monitor

Date:
2012-01-23 18:28
Priority:
3
State:
Open
Submitted by:
Paul Hardy (paul_hardy)
Assigned to:
Nobody (None)
Category:
Group:
Category:
Group:
Summary:
Hex and Quote characters allowed in control file?

Detailed description
I have no problem loading a table using pg_bulkload. 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 because of the quotes.

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.

Anyone out there run across this? have a solution?

Followup

No Followups Have Been Posted

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge