Backlog Courrier

Petite modif à ajouté en cas de duplication de clé ?

ERROR: duplicate key value violates unique constraint "users_baskets_preferences_key"
DETAIL: Key (user_serial_id, group_serial_id, basket_id)=(220, 3, EsignconsBasket) already exists.

CONTEXT: SQL statement "insert into users_baskets_preferences (user_serial_id, group_serial_id, basket_id, display)
select users.id, usergroups.id, user_baskets_secondary.basket_id, TRUE from users, usergroups, user_baskets_secondary
where users.user_id = user_baskets_secondary.user_id and usergroups.group_id = user_baskets_secondary.group_id
order by users.id"
PL/pgSQL function inline_code_block line 20 at SQL statement

La table users_baskets_preferences n'est donc pas créée et peut poser problème par la suite.

Je vous propose une vérification de ce type :

DO $$ BEGIN
  IF (SELECT count(TABLE_NAME)  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user_baskets_secondary') = 1 THEN
    DROP TABLE IF EXISTS users_baskets_preferences;
    CREATE TABLE users_baskets_preferences
    (
      id serial NOT NULL,
      user_serial_id integer NOT NULL,
      group_serial_id integer NOT NULL,
      basket_id character varying(32) NOT NULL,
      display boolean NOT NULL,
      color character varying(16),
      CONSTRAINT users_baskets_preferences_pkey PRIMARY KEY (id),
      CONSTRAINT users_baskets_preferences_key UNIQUE (user_serial_id, group_serial_id, basket_id)
    )
    WITH (OIDS=FALSE);
    INSERT INTO users_baskets_preferences (user_serial_id, group_serial_id, basket_id, display)
    SELECT users.id, usergroups.id, groupbasket.basket_id, TRUE FROM users, usergroups, groupbasket, usergroup_content
    WHERE usergroup_content.primary_group = 'Y' AND groupbasket.group_id = usergroup_content.group_id AND users.user_id = usergroup_content.user_id AND usergroups.group_id = usergroup_content.group_id
    ORDER BY users.id;


-- modification proposée (ouverture du try): 

    BEGIN

-----------------------------  
        insert into users_baskets_preferences (user_serial_id, group_serial_id, basket_id, display)
        select users.id, usergroups.id, user_baskets_secondary.basket_id, TRUE from users, usergroups, user_baskets_secondary
        where users.user_id = user_baskets_secondary.user_id and usergroups.group_id = user_baskets_secondary.group_id
        order by users.id;

-- et ici (fermeture du try):

    EXCEPTION WHEN unique_violation THEN
    END;

-------------------------------

    DROP TABLE IF EXISTS user_baskets_secondary;
  END IF;
END$$;