拆分单个JSON数组列并使用此数据更新多个整型列

-- 假设表结构如下：
-- CREATE TABLE user_info (
--     id SERIAL PRIMARY KEY,
--     scores JSONB,
--     score1 INT,
--     score2 INT,
--     score3 INT
-- );

-- 更新操作
UPDATE user_info
SET score1 = scores->>0,
    score2 = scores->>1,
    score3 = scores->>2
WHERE id = 1;

import psycopg2
import json

# 连接数据库
conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
cur = conn.cursor()

# 假设我们有一个用户ID和对应的JSON数组
user_id = 1
scores_json = '[10, 20, 30]'

# 解析JSON数组
scores = json.loads(scores_json)

# 更新数据库
cur.execute("""
    UPDATE user_info
    SET score1 = %s,
        score2 = %s,
        score3 = %s
    WHERE id = %s;
""", (scores[0], scores[1], scores[2], user_id))

# 提交事务
conn.commit()

# 关闭连接
cur.close()
conn.close()

UPDATE user_info
SET score1 = COALESCE(scores->>0, 0),
    score2 = COALESCE(scores->>1, 0),
    score3 = COALESCE(scores->>2, 0)
WHERE id = 1;