尝试在 PostgreSQL 中使用“CASE”时出现错误

发布于 2025-01-14 07:03:53 字数 1024 浏览 3 评论 0原文

下午好，我正在尝试在 PgAdmin 中运行查询以增加表中的值（如果存在），如果不存在，它将创建行，我收到错误，但

ERROR:  syntax error at or near "CASE"
LINE 1: CASE 
        ^
SQL state: 42601
Character: 1

CASE 
    WHEN EXISTS(SELECT * FROM counter WHERE user_id = '321') THEN
        UPDATE counter
            SET counter = counter + 1
        WHERE user_id = '321'
    ELSE 
        INSERT INTO counter (user_id) VALUES ('321')  -- `counter` column is `SMALLINT DEFAULT 0`
END

我也尝试使用 upsert

INSERT INTO cookies_counter (user_id) VALUES ('321')
ON CONFLICT (user_id) DO 
UPDATE SET counter = counter + 1
WHERE user_id = '321'

它给了我而是一个不同的错误

ERROR:  column reference "counter" is ambiguous
LINE 2: ON CONFLICT (user_id) DO UPDATE SET counter = counter + 1
                                                      ^
SQL state: 42702
Character: 100

原文

Good afternoon, I'm trying to run a query in PgAdmin to increment a value in my table if it exists, if not it will create the row, I'm getting an error however

ERROR:  syntax error at or near "CASE"
LINE 1: CASE 
        ^
SQL state: 42601
Character: 1

CASE 
    WHEN EXISTS(SELECT * FROM counter WHERE user_id = '321') THEN
        UPDATE counter
            SET counter = counter + 1
        WHERE user_id = '321'
    ELSE 
        INSERT INTO counter (user_id) VALUES ('321')  -- `counter` column is `SMALLINT DEFAULT 0`
END

I've also tried using the upsert

INSERT INTO cookies_counter (user_id) VALUES ('321')
ON CONFLICT (user_id) DO 
UPDATE SET counter = counter + 1
WHERE user_id = '321'

It gives me a different error instead

ERROR:  column reference "counter" is ambiguous
LINE 2: ON CONFLICT (user_id) DO UPDATE SET counter = counter + 1
                                                      ^
SQL state: 42702
Character: 100

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

撩起发的微风 2025-01-21 07:03:53

像这样的东西可能会起作用，但这在一定程度上取决于表结构：

CREATE  TABLE counter(user_id INT UNIQUE, counter smallint default 0);
INSERT INTO counter (user_id) 
VALUES ('321')
ON CONFLICT (user_id)
DO UPDATE
SET counter = counter.counter + 1::SMALLINT;

Something like this might work, but it depends a little on the table structure:

CREATE  TABLE counter(user_id INT UNIQUE, counter smallint default 0);
INSERT INTO counter (user_id) 
VALUES ('321')
ON CONFLICT (user_id)
DO UPDATE
SET counter = counter.counter + 1::SMALLINT;

回复收藏 0 原文

~没有更多了~