To ignore duplicate keys during ‘copy from’ in postgresql

by

Use the same approach as you described, but DELETE (or group, or modify …) duplicate PK in the temp table before loading to the main table.

Something like:

CREATE TEMP TABLE tmp_table 
ON COMMIT DROP
AS
SELECT * 
FROM main_table
WITH NO DATA;

COPY tmp_table FROM 'full/file/name/here';

INSERT INTO main_table
SELECT DISTINCT ON (PK_field) *
FROM tmp_table
ORDER BY (some_fields)

Details: CREATE TABLE AS, COPY, DISTINCT ON

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  3. How to use RETURNING with ON CONFLICT in PostgreSQL?
  4. Does PostgreSQL support “accent insensitive” collations?
  5. PostgreSQL DISTINCT ON with different ORDER BY
  6. PostgreSQL: running count of rows for a query ‘by minute’
  7. Split comma separated column data into additional columns
  8. How to delete duplicate entries?
  9. Escaping keyword-like column names in Postgres
  10. Transpose latest rows per user to columns
  11. Delete duplicate rows (don’t delete all duplicate)
  12. GROUP BY + CASE statement
  13. subquery in FROM must have an alias
  14. Compute percents from SUM() in the same SELECT sql query
  15. SQL Between clause with strings columns
  16. Insert an image in postgresql database
  17. Perform this hours of operation query in PostgreSQL
  18. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  19. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  20. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  21. Postgresql Column Not Found, But Shows in Describe
  22. DROP FUNCTION without knowing the number/type of parameters?
  23. Why can’t I use column aliases in the next SELECT expression?
  24. Postgres window function and group by exception
  25. SQL where joined set must contain all values but may contain more
  26. How do I ALTER a PostgreSQL table and make a column unique?
  27. Find records where join doesn’t exist
  28. How to group timestamps into islands (based on arbitrary gap)?
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment