Re: a better way to do a data import?

blackwater dev wrote:

I have a text file that contains 200k rows. These rows are to be imported into our database. The majority of them will already exists while a few are new. Here are a few options I've tried: I've had php cycle through the file row by row and if the row is there, delete it and do a straight insert but that took a while. Now I have php get the row from the text file and then to array_combine with a default array I have in the class so I can have key value pairs. I then take that generated array and do array_diff to the data array I pulled from the db and I then have the columns that are different so I do an update on only those columns for that specific row. This is slow and after about 180,000 rows, php throws a memory error. I'm resetting all my vars to NULL at each iteration so am not sure what's up. Anyone have a better way to do this? In MySQL, I could simply a replace on each row...but not in postgres.

// clone the table. create temporary table x as select * from othertable limit 1; in mysql: load data infile '/path/to/csv' into table 'x'; (http://dev.mysql.com/doc/refman/4.1/en/load-data.html) in postgres: \copy x from '/path/to/csv' (no semi-colon on the end). analyze; // make sure you do this otherwise the next step is going to take forever. step2: delete stuff from the temp table that already exists: delete from x where id in (select id from othertable); or some such variant. step3: insert left over stuff into real table. -- Postgresql & php tutorials http://www.designmagick.com/