Geek asks and answers

Insert or ignore in Postgresql

I have to switch our code from sqlite to postgres. One of the questions I came across is copied below.

INSERT INTO group_phones(group_id, phone_name)
SELECT g.id, p.name 
FROM phones AS p, groups as g
WHERE g.id IN ($add_groups) AND p.name IN ($phones);

The problem occurs when there is a duplicate entry. In this table, the combination of both values ​​must be unique. I used several plpgsql functions in other places to perform update or insert operations, but in this case I can do multiple inserts at once. I am not sure how to write a stored procedure for this. Thanks for all the help from all sql gurus!

+5
source share
2 answers

There are 3 problems .

  • JOIN phones groups, CROSS JOIN, , , . , . 100 100 , 10 000 .

  • (group_id, phone_name)

  • group_phones.

, :

INSERT INTO group_phones(group_id, phone_name)
SELECT i.id, i.name
FROM  (
    SELECT DISTINCT g.id, p.name -- get distinct combinations
    FROM   phones p
    JOIN   groups g ON ??how are p & g connected??
    WHERE  g.id IN ($add_groups)
    AND    p.name IN ($phones)
    ) i
LEFT   JOIN group_phones gp ON (gp.group_id, gp.phone_name) = (i.id, i.name)
WHERE  gp.group_id IS NULL  -- avoid duping existing rows

Concurrency

. , . , - ( ) .

BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
COMMIT;

, . : @depesz SO.

, .

LEFT JOIN tbl ON right_col = left_col WHERE right_col IS NULL

, . ( ),

WHERE NOT EXISTS (SELECT 1 FROM tbl WHERE right_col = left_col)

, , .

IN, @dezso, PostgreSQL.

+11

:

INSERT INTO group_phones(group_id, phone_name)
SELECT DISTINCT g.id, p.name 
FROM phones AS p, groups as g
WHERE 
    g.id IN ($add_groups) 
    AND p.name IN ($phones)
    AND (g.id, p.name) NOT IN (
        SELECT group_id, phone_name
        FROM group_phones
    )
;

DISTINCT , , NOT IN .

. , , , Erwin .

+2

More articles:


All Articles