Compilation error on UPDATE FROM statement on PostgreSQL

[OfekTeken]

SQLDelight Version

2.0.1

SQLDelight Dialect

PostgreSQL

Describe the Bug

Consider the following schema:

CREATE TABLE table(
    table_id BIGSERIAL PRIMARY KEY,
    other_id BIGSERIAL NOT NULL
);

CREATE TABLE other(
    other_id BIGSERIAL PRIMARY KEY,
    other_name varchar(255) UNIQUE NOT NULL
);

The plugin gives the following error: Multiple columns found with name other_id

For this query:

updateOther:
UPDATE table
    SET other_id = o.other_id -- Error here on the first "other_id"
FROM other o
WHERE other_name = :otherName AND table_id = :tableId;

I found a workaround for those with similar needs:

WITH cte(id) AS (
    SELECT other_id FROM other WHERE other_name = :otherName
)
UPDATE table
    SET other_id = cte.id
FROM cte
WHERE table_id = :tableId;

Do notice that if I change cte(id) to cte(other_id) the same error occurs

Stacktrace

(204, 8): Multiple columns found with name other_id
203    UPDATE table
204        SET other_id = o.other_id -- Error here on the first "other_id"
               ^^^^^^^^
205    FROM other o
206    WHERE other_name = :otherName AND table_id = :tableId

[griffio]

🔁 It looks like the same issue as https://github.com/cashapp/sqldelight/pull/4777 for Sqlite

Same fix seems to work for Postgresql dialect - will create a PR 🥨