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

Postgresql – In one query – Insert data, on conflict Update data, and delete data if the condition is met

I have a table with tasks.
And I want to add a task to repeat after a while.
If the task exists in the table, update the task, but if there are more than 3 attempts, delete the task or not insert to the table.

I have a table like this

CREATE TABLE task (
    task_name VARCHAR(10) NOT NULL,
    attempt INTEGER NOT NULL DEFAULT 0,
    delay_to TIMESTAMPTZ DEFAULT NULL,
    UNIQUE(task_name)
);

I want create or delay task if exist.
I have query like this

INSERT INTO task (
    task_name, attempt,delay_to
)
VALUES (
    $1, '0', current_timestamp + (interval '1 seconds' * 600)
)
ON CONFLICT (
    task_name
)
DO UPDATE SET
    delay_to = current_timestamp + ((interval '1 seconds') * 600),
RETURNING
    attempt < 3;

But now I want to delete task if attempt more then 3.
Like this

DELETE FROM task WHERE attempt > 3 AND task = $1;

Can I do it in one query?
And I want to return true if task delayed, otherwise false if task deleted.

Tags:

2

Answers


  1. 0

    I do not think you can directly write a delete statement with "on conflict" clause.
    But instead, you can try writing trigger Before/After trigger for the same.

    Login or Signup to reply.
  2. 0

    Use returning inside of CTEs to accomplish this:

    with inputs (task_name) as (
  values ('task2'), ('task3'), ('task4')
), deletes as (
  delete from task
   where attempt > 3
     and exists (select 1 from inputs
                  where inputs.task_name = task.task_name)
  returning task_name
), upserts as (
  insert into task
  select task_name, 0, current_timestamp + interval '10 minutes'
    from inputs
   where not exists (select 1 from deletes
                      where task_name = inputs.task_name)
  on conflict (task_name)
  do update set delay_to = current_timestamp + interval '10 minutes'
  returning *
)
select * from upserts;

select * from task;

    Working fiddle

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

Back To Top
Search