Insert based on a select, with NOT EXISTS validation with multiple records with Postgresql

Question

Could someone help me, as a select-based Insert would do so by checking if the value already exists , with multiple values ? this way this working, however I would like to insert more than 1 record at once.

      insert into  tb_rel_regra_sit_contrato
        (
            status_acordo_id,
            sit_id,
            new_sit_contrato
        )
      sSELECT 2, 9 , 9
       WHERE  NOT EXISTS
         (SELECT * FROM  tb_rel_regra_sit_contrato WHERE
                         status_acordo_id = 2 and 
                        sit_id = 9  and
                         new_sit_contrato = 9);

Answer 1

You can make your query more dynamic using a time table. type like this:

CREATE TEMPORARY TABLE tmp_tb_rel_regra_sit_contrato
(
    status_acordo_id int,
    sit_id int,
    new_sit_contrato int
)

insert into tmp_tb_rel_regra_sit_contrato
values(2,9,9)

insert into tb_rel_regra_sit_contrato
select 
    * 
from 
    tb_rel_regra_sit_contrato a
    right join
    tmp_tb_rel_regra_sit_contrato i
    on a.status_acordo_id = i.status_acordo_id
    and a.sit_id = i.sit_id
    and a.new_sit_contrato = i.new_sit_contrato
where a.status_acordo_id is NULL

I don’t know what your keys are, so I advise you to change the query above to do Join only by keys.