Question 263986 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert multiple rows using EXISTS condition

I’m trying to create an insert script to insert multiple rows and using "exists" condition, but it’s returning me an error, and I don’t know why,

I have a query like this:

INSERT INTO films (id, code, title, did, date_prod, kind) VALUES
    (1, 'B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    (2, 'HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'),
    (3, 'HG770', 'The Tampopo', 140, DEFAULT, 'Comedy')
WHERE NOT EXISTS (SELECT 1 FROM films WHERE code in ('B6717', 'HG120', 'HG770') 
    AND NOT EXISTS (SELECT 1 FROM films WHERE id in (1,2,3));

I would like to know why this query is not possible, and the correct way to insert multiple rows using multiple "exists" conditions

Tags:

2

Answers


  1. 0

    The way you do this is by placing a unique constraint on code:

    ALTER TABLE films ADD UNIQUE (code);

    Then you can use INSERT ... ON CONFLICT:

    INSERT INTO films (id, code, title, did, date_prod, kind) VALUES
   (1, 'B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
   (2, 'HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'),
   (3, 'HG770', 'The Tampopo', 140, DEFAULT, 'Comedy')
ON CONFLICT DO NOTHING;
    Login or Signup to reply.
  2. 0

    You can use the upsert operation to insert multiple rows without using the EXISTS condition

    The following query may help to insert multiple rows into your table.
    The query is as follows :

    INSERT INTO films (id, code, title, did, date_prod, kind) VALUES
  (1, 'B6717', 'Tampopo', 110, '1985-02-10', 'Comedy')
ON CONFLICT (code) DO UPDATE
  SET title = VALUES(title),
       did = VALUES(did),
       date_prod = VALUES(date_prod),
       kind = VALUES(kind);

    The following query will attempt to insert a row into the films table with the specified values and if there is a conflict it will perform an update on the conflicting row instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search