COPY, \copy with defaults

csn

Well, I thought this was a good idea, but haven't heard anything back. Sxooter, know of a way to do this in Postgres? All I can think of is to create a (PHP) script that parses the file line by line then inserts each row with specified custom default values. Might be able to do it in plpgsql, but might have permission problems with COPY.

I guess this is a feature request since I can't
figure
out a way to do it directly in postgresql (plus
COPY/\copy isn't in SQL specs). Often when I import
data I need to assign foreign key id's, timestamps,
etc. How about a syntax something like:

\copy tablename (col1, col2, col3, fkid=8,
ts1='2004-8-8') from 'data.csv'

or maybe add a defaults clause after "with":

\copy tablename (col1, col2, col3) from 'data.csv'
with defaults (fkid=8,ts1='2004-8-8')

Or if anybody knows of a better/existing way please
feel free. 🙂

CSN

smudgeh

Not sure if this has been resolved or not, but I shall put my 2 pence worth in, in case it helps someone else.

I had this problem recently where I had to import 1.5 million tab delimited rows from a file once a day, into a postgres table.

I looked at looping through the file, and very quickly looked away again due to server performance issues, and the time it took to undertake this process.

What I eventually did, was use COPY to import the data into a temporary table, then manipulate the data once inside postgres. It's not ideal, but in my instance, the entire import process (including dropping/creating indexes, importing data, creating primary key sequence etc.) rarely exceeds 10 minutes.

10 minutes sounds a lot, but the main reason for me using this method was that it puts almost no strain on the system and I found it to be around 500-600% quicker than doing it looping through the file and processing it.