Assign update query value to variable in SQL

Question

I need to insert some rows to tables in PostgreSQL. But before inserting the row into table, I should check whether the record is existing or not by using update statement. If update statement returns 0 rows, then I can insert, otherwise I can skip that insertion. I should write SQL statements for this in .sql file in PostgreSQL.

How to achieve this?

In Oracle we have below format:

declare
  i number;
begin
  update employees set status = 'fired' where name like '%Bloggs';
  i := sql%rowcount;
IF i ==0 THEN
insert statement here
end

How can I achieve this in Postgres?

Got answer using GET DIAGNOSTICS l = ROW_COUNT; in postgre sql — Balakrishna, Aug 05 '14 at 06:49

score 1 · Answer 1 · edited May 23 '17 at 12:20

If concurrency is not a problem for you, and you want to do it in a plpgsql function, rather use the special variable FOUND:

DO
$do$
BEGIN

UPDATE employees SET status = 'fired' WHERE ... ;

IF NOT FOUND THEN
   -- insert statement here
END IF;

END
$do$

Or use a data-modifying CTE:

If concurrency is relevant, read up on proper UPSERT solutions.

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Assign update query value to variable in SQL

1 Answers1