insert if update subquery returns 0 rows

Question

Specs:

PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

I'm on MacOS 13.2.1, using DBeaver IDE (version 23.0.4.202305071722) to connect to the database.

---

I am trying to re-write what is essentially an upsert query. Since the database I'm using is on an older postgres version, I am unable to use the more recent upsert syntax of ON CONFLICT DO ....

The table I am upserting against is setup to have an id column of type serial4, such that every row has a unique id number which is set by default. Additionally, there is a unique constraint on col3 and col4 so that there are no two rows with the same col3 and col4 values.

Here is an SQLFiddle showing an example version of this table and the current (working) upsert query: http://sqlfiddle.com/#!15/94a73/1

The query I currently have, which does work, uses with subquery as (...) syntax/structure. However, I would prefer to have the query not use that syntax, and instead have the subquery inside the insert query.

Here is the existing query:

with upsert as (
    update tbl
    set col1='123', col2='456'
    where id=(
        select id
        from tbl
        where col3='baz'
        and col4='bat'
    )
    returning *
)
insert into tbl (col1, col2, col3, col4)
select '123', '456', 'baz', 'bat'
where not exists (select * from upsert)

The result of this query is that if there is a row in the table where col3='baz' and col4='bat', that row's values for col1 and col2 are updated to 123 and 456, respectively. If there is no such row, a new row is inserted with those values.

---

The re-structured query I have right now is as follows:

insert into tbl (col1, col2, col3, col4)
select 'foo', 'bar', 'baz', 'bat'
where not exists (
    select * from (
        update tbl
        set col1='foo', col2='bar'
        where id=(
            select id from tbl
            where col3='baz'
            and col4='bat'
        )
        returning *
    ) upsert
) 

When I try to run the query, I get the following error message:

ERROR: syntax error at or near "set"

I tried changing the select 'foo', 'bar', 'baz', 'bat' to values ('foo', 'bar', 'baz', 'bat'), but that gives a different error message:

ERROR: syntax error at or near "where"

I believe the issue is that I'm messing up my syntax somewhere, but I'm unable to discern where.

---

To reiterate my question -- is it possible to re-structure the working query I have so that it doesn't use the with subquery as ... syntax? If so, how can I do that?

Thanks in advance.

Answer 1

As per this comment, Postgres does not support running an update as a subquery. As such, I do need to use it in a CTE, which is the with subquery as (...) syntax that I was referring to in my question. For future reference of anyone else who may be interested, I found helpful relevant information about CTEs (Common Table Expressions) here.

Since I did not know the specific terminology (CTE) to describe the existing query I was working with, I was unable to find useful information to answer my question. The above-linked comment did resolve my question by providing that missing piece of knowledge and, as such, I will accept it as an answer should it be posted as such. Thanks again for the assistance!