Insert from Another Table + Removing Duplicate Entries in Postgres

Question

I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code.

I have two tables I'm working with: mytesttable, which contains test entries and entries, which is my main entries. The entries table has a primary key of (username, date, time, x0, x1, x2).

Code that I'm trying to implement from this post: Optimal way to ignore duplicate inserts?

BEGIN;
INSERT INTO entries (username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;
EXCEPTION
WHEN unique_violation
THEN
/* ignore */;
END;

And my error message:

ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (username, date, "time", x0, x1, x2)=(duplicate key information) already exists.
ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION

UPDATE

I tried to implement one of the suggestions from below with no luck. The documentation for BEGIN says that you need a semicolon and I'd get a syntax error on the INSERT without it, so I kept it.

BEGIN;
INSERT INTO log (username, date, time, x0, x1...)
SELECT username, date, time, x0, x1, x2...
FROM stevietable;
EXCEPTION
WHEN unique_violation
    THEN RAISE NOTICE 'I am ignoring a unique violation';
END;

And the error:

psql:/tmp/test.sql:4: ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (duplicate key information) already exists.
psql:/tmp/test.sql:7: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION
        ^

Answer 1

Why not just use distinct on like this:

INSERT INTO entries(username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT DISTINCT ON (username, date, time, x0, x1, x2) username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;

?

But beware that this will retain the first row for every distinct combination -- which might not be the right thing without an order by clause this. But since you don't order in your question I presume you either don't care or haven't thought that far yet.

Answer 2

Instead of handling the error try to avoid the exception by not inserting those rows that already exist:

INSERT INTO entries (username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT t.username, t.date, t.time, t.x0, t.x1, t.x2, t.x3, t.x4, t.x5, t.x6
FROM mytesttable t
where not exists (select 1 
                  from entries e
                  where e.username = t.username
                    and e."date" = t."date" 
                    and e."time" = t."time"
                    and e.x0 = t.x0 
                    and e.x1 = t.x1
                    and e.x2 = t.x2);

Note that this might still generate errors if done from multiple concurrent transactions. So it's more suited for a one time bulk load.

Answer 3

An alternative is to find the duplicates BEFORE insertion, join to the list of duplicates and only insert the ones not found in the duplicate list.

I'm an Oracle guy, so here's Oracle syntax which is not identical to PostgreSQL but should illustrate the point:

WITH qry_find_dups as (
   SELECT 
     username, date, time, x0, x1, x2, COUNT(*) AS dupcount
   FROM mytesttable
   GROUP BY
     username, date, time, x0, x1, x2
)
INSERT into entries 
   (username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT 
   username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable
WHERE (username, date, time, x0, x1, x2) in  
      (SELECT                             /* sub-optimal match on PK list */
         username, date, time, x0, x1, x2
       FROM
         qry_find_dups
       WHERE
         dupcount = 1                /* test to extract only singletons */
      ) ;

In this example, the WITH clause creates a named sub-query "qry_find_dups" that counts occurrences of PK columns. The INSERT clause matches against to that sub-query when choosing which full rows to insert.

A similar result can be accomplished by creating a sub-query in the FROM clause, then joining to it with the columns of the combined key and keeping the WHERE clause criteria of dupcount = 1...this would look like:

    INSERT into entries 
       (username, date, time, x0, x1, x2, x3, x4, x5, x6)
    SELECT 
       username, date, time, x0, x1, x2, x3, x4, x5, x6
    FROM mytesttable A, 
         (SELECT 
              username, date, time, x0, x1, x2, COUNT(*) AS dupcount
          FROM mytesttable
          GROUP BY
             username, date, time, x0, x1, x2
          ) B
    WHERE B.dupcount = 1  and        /* test to extract only singletons */
          A.date = B.date and      /* join on all Key columns  */
          A.time = B.time and
          A.x0 = B.x0 and
          A.x1 = B.x1 and
          A.x2 = B.x2 ;

Answer 4

UPDATED

The reason for the errors is that the code that you were trying to reproduce in Optimal way to ignore duplicate inserts? is the body of a PL/pgSQL procedure or of a SQL DO statement, in which the EXCEPTION block can be used to do something when a transaction fails.

Instead you wrote the three statements as SQL statements, of which the first is interpreted as the BEGIN [TRANSACTION] statement (correct); the second as a single INSERT statement (which fails because of duplicate transaction); the third as statement which begins with the EXCEPTION keyword, and since there is no such statement in SQL, this causes the syntax error.

In any case the approach of using a DO statement or stored function with an empty body after EXCEPTION is wrong, since the transaction will fail when a duplicate is encountered, the execution is immediately terminated, and no insertion is performed. So, follow the advices of the other answers, if those work for you.