How to fix error: ambiguous column reference?

Question

PostgreSQL

This is what the request looks like

INSERT INTO users (balance, steamid, name, avatar) 
        VALUES (0, $1, $2, $3) 
        ON CONFLICT (steamid) DO 
        UPDATE SET name = $2, avatar = $3 WHERE steamid = $1;

This is what the table looks like

CREATE TABLE users (
id BIGSERIAL NOT NULL PRIMARY KEY,
balance INTEGER NOT NULL,
steamid VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
avatar VARCHAR(100) NOT NULL,
tradelink_token VARCHAR(20),
tradelink_partner VARCHAR(20)
);

And here's the error:
error: ambiguous reference to column "steamid"

Answer 1

UPDATE SET name = $2, avatar = $3 WHERE users.steamid = $1;

Answer 2

Because instead of waiting for an answer, you need to read the documentation!
Normally your request should look like this:

INSERT INTO users (balance, steamid, name, avatar) 
        VALUES (0, $1, $2, $3) 
        ON CONFLICT (steamid) DO 
        UPDATE SET name = excluded.name, 
              avatar = excluded.avatav;

In this case, you do not need a condition for update, since a conflict occurred on it. And the excluded service word applies the data set on which the conflict occurred.
You can read more about how it works in the documentation .