I having syntax error for the following sql which spent an hour but cant find any answer form postgres documentation.
CREATE TABLE transaction (userid SMALLINT, point INT, timestamp TIMESTAMPTZ);
CREATE TABLE point (userid SMALLINT PRIMARY KEY, balance1 INT, balance2 INT);
CREATE TABLE settings (userid SMALLINT, bonus INT);
INSERT INTO settings VALUES (1, 10); -- sample data
WITH trans AS (
INSERT INTO transaction (userid, point, timestamp)
VALUES ($1, $2, NOW())
INSERT INTO point (userid, balance1)
SELECT userid, point
FROM trans -- first time
ON CONFLICT (userid) DO UPDATE
SET balance1=point.balance1 + excluded.balance1,
balance2=point.balance2 + excluded.balance1 + settings.bonus
FROM settings -- tried USING, not work
It's not totally clear to me what you are trying to do. The way I understand it, is that you are trying to use the value from
settings.bonus when updating the row in case
on conflict kicks in.
This can be done using a co-related sub-query in the
WITH trans (userid, point, timestamp) AS ( INSERT INTO transaction (userid, point, timestamp) VALUES (1, 1, NOW()) RETURNING * ) INSERT INTO point (userid, balance1) SELECT trans.userid, trans.point FROM trans ON CONFLICT (userid) DO UPDATE SET balance1 = point.balance1 + excluded.balance1, balance2 = point.balance2 + excluded.balance1 + (select s.bonus from settings s where s.userid = excluded.userid)
Note however, that
balance2 will never be incremented this way because on the first insert it will be
null subsequent updates will try to add something to
null but any expression involving null yields null (
null + 5 is null).
So you either need to insert
0 when doing the insert the first time, or use
coalesce() when doing the update, e.g.
balance2 = coalesce(point.balance2, 0) + excluded.balance1 + (select ...);
If it's possible that there is no row in
settings for that user, then you need to apply
coalesce() on the result of the sub-query as well:
... excluded.balance1 + coalesce( (select ... ), 0)
Also: you used
excluded.point in your question which is incorrect.
excluded refers to the target table
point but that does not have a column point - you probably meant: excluded.balance1 (as that is the column into which the inserted
trans.point will go)
timestamp is a horrible name for a column. For one because it's a keyword, but more importantly because it does not document what the column is for. Does it mean "created at"? "valid until"? "start at"? "obsolete from"? Something entirely different?