How to bulk insert only new rows in PostreSQL

by

Import data

COPY everything to a temporary staging table and insert only new titles into your target table.

CREATE TEMP TABLE tmp(title text);

COPY tmp FROM 'path/to/file.csv';
ANALYZE tmp;

INSERT INTO tbl
SELECT DISTINCT tmp.title
FROM   tmp 
LEFT   JOIN tbl USING (title)
WHERE  tbl.title IS NULL;

IDs should be generated automatically with a serial column tbl_id in tbl.

The LEFT JOIN / IS NULL construct disqualifies already existing titles. NOT EXISTS would be another possibility.

DISTINCT prevents duplicates in the incoming data in the temporary table tmp.

ANALYZE is useful to make sure the query planner picks a sensible plan, and temporary tables are not analyzed by autovacuum.

Since you have 3 million items, it might pay to raise the setting for temp_buffer (for this session only):

SET temp_buffers = 1000MB;

Or however much you can afford and is enough to hold the temp table in RAM, which is much faster. Note: must be done first in the session – before any temp objects are created.

Retrieve IDs

To see all IDs for the imported data:

SELECT tbl.tbl_id, tbl.title
FROM   tbl
JOIN   tmp USING (title)

In the same session! A temporary table is dropped automatically at the end of the session.

More Related Contents:

  1. How do I (or can I) SELECT DISTINCT on multiple columns?
  2. How to delete duplicate rows without unique identifier
  3. PostgreSQL multi INSERT…RETURNING with multiple columns
  4. INSERT rows into multiple tables in a single query, selecting from an involved table
  5. Export specific rows from a PostgreSQL table as INSERT SQL script
  6. postgresql: INSERT INTO … (SELECT * …)
  7. Bulk/batch update/upsert in PostgreSQL
  8. SQL Populate table with random data
  9. Solutions for INSERT OR UPDATE on SQL Server
  10. Auto increment table column
  11. ORDER BY the IN value list
  12. How to list table foreign keys
  13. Two SQL LEFT JOINS produce incorrect result
  14. Postgresql GROUP_CONCAT equivalent?
  15. What is the maximum number of columns in a PostgreSQL select query
  16. Pass multiple values in single parameter
  17. Custom SERIAL / autoincrement per group of values
  18. How do I determine the last day of the previous month using PostgreSQL?
  19. PostgreSQL: encoding problems on Windows when using psql command line utility
  20. How much disk-space is needed to store a NULL value using postgresql DB?
  21. Aggregate a single column in query with many columns
  22. Oracle insert if row does not exist
  23. Aggregating (x,y) coordinate point clouds in PostgreSQL
  24. PostgreSQL convert columns to rows? Transpose?
  25. Does Postgres support nested or autonomous transactions?
  26. PostgreSQL delete with inner join
  27. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  28. Update a column of a table with a column of another table in PostgreSQL
  29. Check if NULL exists in Postgres array
  30. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Leave a Comment