0

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
        ^
  • This isn't going to work as you expect. Even if you figure out the syntax, ignoring the error isn't going to 'continue' like you might expect from a for loop in python. The entire INSERT is one statement - everything will roll back and none of the valid rows will be transferred if any error occurs. You're going to catch that error but won't be able to avoid the rolling back. (This should be a comment but DBA is requiring I have 50 reputation first). – user2093082 Jun 24 '15 at 4:06
  • Use distint on. See my answer. – Colin 't Hart Jun 24 '15 at 5:12
  • 1
    Exception handling is only available in PL/pgSQL, not in plain SQL - you need to write a stored function to catch an exception. In PL/pgSQL, BEGIN starts a block and does not need a ;. You are confusing BEGIN TRANSACTION; (which is a SQL statement) with the BEGIN from PL/pgSQL: postgresql.org/docs/current/static/plpgsql-structure.html – a_horse_with_no_name Jun 24 '15 at 5:36
0

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.

  • 1
    The reason DISTINCT won't work in this case is that the KEY only uses six of the ten fields being inserted. The duplication error comes from (username, date, "time", x0, x1, x2) whereas there may be multiple distinct sets in columns 1 through 10 that have repeating columns 1 through 6. – JasonInVegas Jun 23 '15 at 21:44
  • Check edited answer. – Colin 't Hart Jun 23 '15 at 22:23
  • Now that looks like it'll work. – JasonInVegas Jun 24 '15 at 0:08
  • Too bad ORACLE doesn't support that DISTINCT ON clause, I could use it a lot! -- but this question: stackoverflow.com/questions/10515391/… uses analytic functions such as RANK to accomplish similar results. – JasonInVegas Jun 24 '15 at 0:14
  • This question is about Postgres, not Oracle. – Colin 't Hart Jun 24 '15 at 5:10
0

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.

0

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 ;
  • If you are sure you will always only want dupcount = 1 then you can include the criteria inside either the WITH clause or the FROM clause examples by appending HAVING COUNT(*) = 1 after the GROUP BY syntax. Then the WHERE clause singleton dupcount = 1 is no longer needed. – JasonInVegas Jun 23 '15 at 22:15
0

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.

  • EDIT: there are two errors reported...the one fired from the duplicate PK, then the 2nd error from the syntax of the EXCEPTION block. I agree with Renzo and if the EXCEPTION block syntax is fixed, the rows without duplicate keys should insert correctly. – JasonInVegas Jun 23 '15 at 21:46
  • The first error (duplicate key) is given because the exception handling does not handle the exception (for the syntax error). If the syntax where corrected then the duplicate key violation would be ignored, which is exactly what @Stevie asked for. – Renzo Jun 23 '15 at 21:49
  • @Renzo - I tried to remove the semicolon before BEGIN and EXCEPTION with no improvement. I'm adding an update now. – Stevie Jun 24 '15 at 3:26
  • @Stieve, the begin you cite in your addition is the 'BEGIN TRANSACTION' statement, while I was thinking that you where writing the body of a stored function written in PL/pgSQL, postgresql.org/docs/9.4/static/plpgsql-control-structures.html or a SQL 'DO' statement with BEGIN END postgresql.org/docs/9.4/static/sql-do.html ). In any case, if the above statement is inside a transaction, the transaction will be aborted even if the THEN after EXCEPTION is empty, and the solution is wrong. So, look at the other answers. – Renzo Jun 24 '15 at 4:56
  • @Renzo Thanks for the help, I'm more familiar with other languages like Java and Python and assumed that EXCEPTION would properly catch that exception rather than just aborting the transaction! Will try the other solutions. – Stevie Jun 24 '15 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.