Coalesce sentence containing an insert into clause fails in PostgreSQL

Question

This is my trivial test table,

create table test (
  id          int not null generated always as identity,
  first_name. varchar,

  primary key (id),
  unique(first_name)
);

As an alternative to insert-into-on-conflict sentences, I was trying to use the coalesce laziness to execute a select whenever possible or an insert, only when select fails to find a row.

coalesce laziness is described in documentation. See https://www.postgresql.org/docs/current/functions-conditional.html

Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.

I also want to get back the id value of the row, having being inserted or not.

I started with:

select coalesce (
  (select id from test where first_name='carlos'),
  (insert into test(first_name) values('carlos') returning id)
);

but an error syntax error at or near "into" was found. See it on this other DBFiddle https://www.db-fiddle.com/f/t7TVkoLTtWU17iaTAbEhDe/0

Then I tried:

select coalesce (
  (select id from test where first_name='carlos'),
  (with r as (
    insert into test(first_name) values('carlos') returning id
   ) select id from r
  )
);

Here I am getting a WITH clause containing a data-modifying statement must be at the top level error that I don't understand, as insert is the first and only sentence within the with.

I am testing this with DBFiddle and PostgreSQL 13. The source code can be found at https://www.db-fiddle.com/f/hp8T1iQ8eS4wozDCBhBXDw/5

Answer 1

Different method: chained CTEs:

---

CREATE TABLE test
        ( id          INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY
        , first_name VARCHAR UNIQUE
        );

WITH sel AS (
        SELECT id FROM test WHERE first_name = 'carlos'
        )
, ins AS (
        INSERT INTO test(first_name) 
        SELECT 'carlos'
        WHERE NOT EXISTS (SELECT 1 FROM test WHERE first_name = 'carlos')
        RETURNING id
        )
, omg AS (
        SELECT id FROM sel
        UNION ALL 
        SELECT id FROM ins
        )
SELECT id
FROM omg
        ;