Handling unique constraint exceptions caused by inserting duplicate values

Question

I have this table (DDL):

CREATE TABLE corpname_skill
(
  id                                      SERIAL  NOT NULL
    CONSTRAINT corpname_skill_pkey
    PRIMARY KEY,
  guid                                    UUID,
  user_id                                 VARCHAR(50),
  user_strenght_area_name_1               VARCHAR(300),
  user_strenght_area_name_2               VARCHAR(300),
  user_strenght_area_name_3               VARCHAR(300),
  user_strenght_area_name_4               VARCHAR(300),
  user_development_area_name_1            VARCHAR(300),
  user_development_area_name_2            VARCHAR(300),
  user_development_area_name_3            VARCHAR(300),
  user_development_area_name_4            VARCHAR(300),
  line_manager_strenght_area_name_1       VARCHAR(300),
  line_manager_strenght_area_name_2       VARCHAR(300),
  line_manager_strenght_area_name_3       VARCHAR(300),
  line_manager_strenght_area_name_4       VARCHAR(300),
  line_manager_strenght_area_comment_1    TEXT    NOT NULL,
  line_manager_strenght_area_comment_2    TEXT    NOT NULL,
  line_manager_strenght_area_comment_3    TEXT    NOT NULL,
  line_manager_strenght_area_comment_4    TEXT    NOT NULL,
  line_manager_development_area_name_1    VARCHAR(300),
  line_manager_development_area_name_2    VARCHAR(300),
  line_manager_development_area_name_3    VARCHAR(300),
  line_manager_development_area_name_4    VARCHAR(300),
  line_manager_development_area_comment_1 TEXT,
  line_manager_development_area_comment_2 TEXT,
  line_manager_development_area_comment_3 TEXT,
  line_manager_development_area_comment_4 TEXT,
  line_manager_final_comment              TEXT,
  line_manager_step_status                VARCHAR(300),
  company_profile_id                         INTEGER NOT NULL
    CONSTRAINT corpname_skill_company_profile_id_key
    UNIQUE
    CONSTRAINT corpname_skill_company_profile_id_ae37e790_fk_corpname
    REFERENCES corpname_corpnameuserprofile
      DEFERRABLE INITIALLY DEFERRED,
  task_closing_date                       DATE
);

I'm trying to do an insertion there:

INSERT INTO corpname_skill (
    guid, 
    user_id, 
    user_strenght_area_name_1, 
    user_strenght_area_name_2, 
    user_strenght_area_name_3, 
    user_strenght_area_name_4, 
    user_development_area_name_1, 
    user_development_area_name_2, 
    user_development_area_name_3, 
    line_manager_strenght_area_name_1, 
    line_manager_strenght_area_name_2, 
    line_manager_strenght_area_name_3, 
    line_manager_strenght_area_name_4, 
    line_manager_strenght_area_comment_1, 
    line_manager_strenght_area_comment_2, 
    line_manager_strenght_area_comment_3, 
    line_manager_strenght_area_comment_4, 
    line_manager_development_area_name_1, 
    line_manager_development_area_name_2, 
    line_manager_development_area_name_3, 
    line_manager_development_area_comment_1, 
    line_manager_development_area_comment_2, 
    line_manager_development_area_comment_3, 
    line_manager_final_comment, 
    line_manager_step_status, 
    task_closing_date, 
    company_profile_id
)
SELECT
    t.guid, 
    t.user_id, 
    t.user_strenght_area_name_1, 
    t.user_strenght_area_name_2, 
    t.user_strenght_area_name_3, 
    t.user_strenght_area_name_4, 
    t.user_development_area_name_1, 
    t.user_development_area_name_2, 
    t.user_development_area_name_3, 
    t.line_manager_strenght_area_name_1, 
    t.line_manager_strenght_area_name_2, 
    t.line_manager_strenght_area_name_3, 
    t.line_manager_strenght_area_name_4, 
    t.line_manager_strenght_area_comment_1, 
    t.line_manager_strenght_area_comment_2, 
    t.line_manager_strenght_area_comment_3, 
    t.line_manager_strenght_area_comment_4, 
    t.line_manager_development_area_name_1, 
    t.line_manager_development_area_name_2, 
    t.line_manager_development_area_name_3, 
    t.line_manager_development_area_comment_1, 
    t.line_manager_development_area_comment_2, 
    t.line_manager_development_area_comment_3, 
    t.line_manager_final_comment, 
    t.line_manager_step_status, 
    t.task_closing_date, 
    t.company_profile_id

FROM corpname_skill o
RIGHT JOIN corpname_skill_new t
ON t.company_profile_id = o.company_profile_id
WHERE o.company_profile_id IS NULL

but I'm experiencing a unique contraint error.

[23505] ERROR: duplicate key value violates unique constraint "corpname_skill_company_profile_id_key" Detail: Key (company_profile_id)=(256871) already exists.

I wrapped the sql statement using a BEGIN...EXCEPTION...END; block described in the postgres docs:

BEGIN
    INSERT INTO corpname_skill (
        guid, 
        user_id, 
        user_strenght_area_name_1, 
        user_strenght_area_name_2, 
        user_strenght_area_name_3, 
        user_strenght_area_name_4, 
        user_development_area_name_1, 
        user_development_area_name_2, 
        user_development_area_name_3, 
        line_manager_strenght_area_name_1, 
        line_manager_strenght_area_name_2, 
        line_manager_strenght_area_name_3, 
        line_manager_strenght_area_name_4, 
        line_manager_strenght_area_comment_1, 
        line_manager_strenght_area_comment_2, 
        line_manager_strenght_area_comment_3, 
        line_manager_strenght_area_comment_4, 
        line_manager_development_area_name_1, 
        line_manager_development_area_name_2, 
        line_manager_development_area_name_3, 
        line_manager_development_area_comment_1, 
        line_manager_development_area_comment_2, 
        line_manager_development_area_comment_3, 
        line_manager_final_comment, 
        line_manager_step_status, 
        task_closing_date, 
        company_profile_id
    )
    SELECT
        t.guid, 
        t.user_id, 
        t.user_strenght_area_name_1, 
        t.user_strenght_area_name_2, 
        t.user_strenght_area_name_3, 
        t.user_strenght_area_name_4, 
        t.user_development_area_name_1, 
        t.user_development_area_name_2, 
        t.user_development_area_name_3, 
        t.line_manager_strenght_area_name_1, 
        t.line_manager_strenght_area_name_2, 
        t.line_manager_strenght_area_name_3, 
        t.line_manager_strenght_area_name_4, 
        t.line_manager_strenght_area_comment_1, 
        t.line_manager_strenght_area_comment_2, 
        t.line_manager_strenght_area_comment_3, 
        t.line_manager_strenght_area_comment_4, 
        t.line_manager_development_area_name_1, 
        t.line_manager_development_area_name_2, 
        t.line_manager_development_area_name_3, 
        t.line_manager_development_area_comment_1, 
        t.line_manager_development_area_comment_2, 
        t.line_manager_development_area_comment_3, 
        t.line_manager_final_comment, 
        t.line_manager_step_status, 
        t.task_closing_date, 
        t.company_profile_id

    FROM corpname_skill o
    RIGHT JOIN corpname_skill_new t
    ON t.company_profile_id = o.company_profile_id
    WHERE o.company_profile_id IS NULL
EXCEPTION 
    WHEN SQLSTATE '23505' THEN RAISE NOTICE 'skipped row';
END;

however the error is not trapped, and I keep experiencing the same error. I tried also with the ON CONFLICT clause instead of this, but I got a syntax error. What I'd like to achieve is a way to skip this error and continue with the INSERT, so in this case skip the row that is causing the error.

Any help on that?

Take a look at https://stackoverflow.com/help/mcve before you ask another question. — jarlh, Mar 06 '18 at 14:07
`user_strenght_area_name_1, user_strenght_area_name_2, user_strenght_area_name_3, user_strenght_area_name_4, ` -->> please lookup https://en.wikipedia.org/wiki/First_normal_form — joop, Mar 06 '18 at 15:08

Erwin Brandstetter · Answer 1 · 2018-03-06T14:26:21.190

You seem to be confusing the SQL statement BEGIN (to start a transaction) with the BEGIN and EXCEPTION keywords of the block-structured procedural language PL/pgSQL.

There is no EXCEPTION command in plain SQL.

To deal with duplicate key errors in INSERT operations, the ON CONFLICT clause will be your best option. Basic syntax:

INSERT ... ON CONFLICT DO NOTHING;

Details:

What happens with duplicates when inserting multiple rows?

Handling unique constraint exceptions caused by inserting duplicate values

1 Answers1