If exists update Else Insert in table with foreign key postgres

Asked

Viewed 999 times

0

I need to do an Insert in a table with a foreign key, so if this record does not exist in the other table I need to do an Insert, can I do this operation using if exists? Some solution?

sql postgresql plpgsql

1 answer

1

You can use the IF EXISTS, checking if the value exists in the table with FK:

IF NOT EXISTS (SELECT 1 FROM TabelaFK WHERE campo = valor) THEN
  -- insere os dados na tabela "FK"
END IF;

Reference (in English) to PostgreSQL: EXIST

It can also be done like this:

declare 
tot integer;

SELECT count(*) INTO tot
FROM forma_pagamento WHERE descricao like '%dinheiro%'

IF tot> 0 THEN
   insert into forma_pagamento (id_conta_preferencial, id_operadora_cartao, descricao, ativo, caixa, conta, cartao, cheque_proprio, cheque_terceiro) values (2, 2, 'dinheiro', true, false, false, false, false, false)
END IF;

  • In postgres it seems that this syntax does not work, look at my sql that is showing error: if not exists ( select id from forma_pagamento Where Descricao like '%dinheiro%') then Insert into forma_pagamento (id_conta_preferencial, id_operadora_cartao, Descricao, ativo, caixa, conta, cartao, cheque_proprio, cheque_terceiro) values (2, 2, 'cash', true, false, false, false, false, false) end if

  • 1

    You are using like to compare? And if you bring more than one record, which one will get the ID? Try to limit the result by adding at the end of your select that: LIMIT 1

  • I added another solution that can solve the limit does not work

  • ERROR: syntax error at or near "integer" LINE 1: declare tot integer;

  • Seems to be missing one BEGIN before the SELECT and a END at the end, after the END IF;. Missing a semicolon also at the end of the querys.

Browser other questions tagged sql postgresql plpgsql

You are not signed in. Login or sign up in order to post.