Обработка уникальных исключений ограничений, вызванных вставкой повторяющихся значений

Question

Обработка уникальных исключений ограничений, вызванных вставкой повторяющихся значений

У меня есть эта таблица (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
);

Я пытаюсь сделать вставку там:

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

но я испытываю уникальную ошибку противопоказания.

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

Я завернул SQL заявление с помощью BEGIN...EXCEPTION...END; Блок описан в документации postgres:

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;

однако ошибка не поймана, и я продолжаю испытывать ту же самую ошибку. Я пытался также с ON CONFLICT вместо этого, но я получил синтаксическую ошибку. То, что я хотел бы достичь, это способ пропустить эту ошибку и продолжить с INSERT, поэтому в этом случае пропустите строку, которая вызывает ошибку.

Любая помощь по этому поводу?

0

sql postgresql duplicates postgresql-9.6 django-postgresql

Источник

user998967 06 мар '18 в 14:03

1 ответ

Другие вопросы по тегам sql postgresql duplicates postgresql-9.6 django-postgresql

user939860 06 мар '18 в 14:16 2018-03-06 14:16 · Answer 1 · 2018-03-06 14:16

Вы, кажется, путаете оператор SQL BEGIN (чтобы начать транзакцию) с BEGIN а также EXCEPTION ключевые слова блочно-структурированного процедурного языка PL / pgSQL.

Здесь нет EXCEPTION Команда в простом SQL.

Чтобы справиться с дублированием ключевых ошибок в INSERT операции, ON CONFLICT пункт будет вашим лучшим вариантом. Основной синтаксис:

INSERT ... ON CONFLICT DO NOTHING;

Подробности:

Что происходит с дубликатами при вставке нескольких строк?