Re: copy from questions

On 12/19/2012 08:13 AM, Kirk Wythers wrote:
> I am using version 9.1 and have a large number of files to insert. I am trying to use a simple COPY FROM command but have a couple questions.
>
> 1. There are a small number of instances where there are duplicate records that are being caught by the primary key (as it is supposed to do). However, the duplicate records are artifacts of a recording system and can be ignored. I would like to be able to simply skip the duplicate or UPDATE the table with the duplicate… Anything that allows the COPY FROM to proceed while adding only one of the duplicate records to the table.
>
> 2. SInce I have several hundred files to perform a COPY FROM on, I'd like to automate the import in some way… sort of a, grab all files in the directory approach:
>
> COPY newtable FROM '/directory_of_files/*' WITH CSV HEADER DELIMITER AS ',' NULL AS 'NA';
>
>
I suppose you could use a trigger to check each record before inserting
but that is likely to be inefficient for bulk loads. A quick bash loop
is probably your best bet. Something along the lines of:

for inputfile in /infiledirectory/*.csv
do
cat inputfile | psql [connection-params] -c '\copy rawinput from
stdin csv header...'
done

This imports everything into a "staging" table (I called it rawinput).
From there you can create your final table with SELECT DISTINCT...

For speed make sure that you create your staging table as "unlogged".

Cheers,
Steve