At now I use code like that

create function something()
returns boolean
   if exists (select 1 from some_table where some_cond) then
       return false;
   end if;

   insert into some_table (value)
   values (2)
   -- on conflict do !!! return false
   returning true;
language 'plpgsql';

Is there a way to return value from on conflict section without defining a variable or temp table in one query?


You can handle exceptions.

create table test (id int primary key, data text);

insert into test values (1,'a'), (2,'b'), (3, 'c');

create or replace function test_error(_id int, _data text)
returns boolean as $res$
declare res boolean;
    res = true;
        -- uncomment to check other exception 
        --select 1 / 0;
        insert into test values (_id, _data);
    when unique_violation then
        res = false;
    when others then
        raise exception 'Other exception';
    return res;
$res$ language plpgsql;
select test_error(4, 'd');

| test_error |
| :--------- |
| t          |

If you try to insert a duplicate key:

select test_error(1, 'n');

| test_error |
| :--------- |
| f          |

db<>fiddle here

  • I heard that exceptions are performance heavy things. And do you shadow another exceptions with "other exception" message? – Herrgott Jul 28 '19 at 23:50
  • Just to show how to get other exceptions than UNIQUE_VIOLATION – McNets Jul 29 '19 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.