for https://twitter.com/ponkotuy/status/999192592066596866

sample.sql

create temp table hoge(id integer, name text);

  with hoge_candidate(id, name) AS (values(1, 'hoge'))
     , inserted_hoge AS (
insert into hoge
select hoge_candidate.*
  from hoge right outer join hoge_candidate using(id)
 where hoge.id is null returning *)

select * from inserted_hoge
 union
select hoge.* from hoge join hoge_candidate using(id);
-- id | name
-- ----+------
-- 1 | hoge
-- (1 row)


  with hoge_candidate(id, name) AS (values(1, 'piyo'))
     , inserted_hoge AS (
insert into hoge
select hoge_candidate.*
  from hoge right outer join hoge_candidate using(id)
 where hoge.id is null returning *)

select * from inserted_hoge
 union
select hoge.* from hoge join hoge_candidate using(id);
-- id | name
-- ----+------
-- 1 | hoge
-- (1 row)

  with hoge_candidate(id, name) AS (values(2, 'fuga'))
     , inserted_hoge AS (
insert into hoge
select hoge_candidate.*
  from hoge right outer join hoge_candidate using(id)
 where hoge.id is null returning *)

select * from inserted_hoge
 union
select hoge.* from hoge join hoge_candidate using(id);
-- id | name
-- ----+------
-- 2 | fuga
-- (1 row)

select * from hoge;
-- id | name
-- ----+------
-- 1 | hoge
-- 2 | fuga
-- (2 rows)