Insert from Another Table + Removing Duplicate Entries in Postgres 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
        ^
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.