时间戳

首页 > 解决方案 > 如果相同的值，则从另一个表更新 Postgres 表的列值，否则为不匹配的值插入新行

sql - 如果相同的值，则从另一个表更新 Postgres 表的列值，否则为不匹配的值插入新行

问题描述

我有这样的表。Table-b：

ID 设备序列号 时间差异
1 636363636890 2156
2 636363636891 27
3 636363636893 178
4 6625839827 53
5 6625839828 28

我有一些来自另一个表的查询，table-b 通过这个查询

SELECT 
    deviceSerial, 
    DATE_PART('day', MAX(createdat)::timestamp - MIN(createdat)::timestamp)  * 24 + 
         DATE_PART('hour', MAX(createdat)::timestamp - MIN(createdat)::timestamp) * 60 +
         DATE_PART('minute', MAX(createdat)::timestamp - MIN(createdat)::timestamp) AS time_difference  
FROM 
    table-a 
GROUP BY 
    deviceserial;

查询：

| deviceSerial | time_difference |
|--------------|-----------------|
| 636363636890 | 123             |
| 636363636891 | 3               |
| 636363636893 | 178             |
| 6625839827   | 29              |
| 6625839828   | 4               |
| 6625839829   | 10              |

现在我想从表 a 中插入值（查询），如果设备序列列匹配，否则设备序列不匹配为不匹配的记录插入另一条记录。我想要 time_difference 值的总和。我想要这样的东西。

| id | deviceSerial | time_difference |
|----|--------------|-----------------|
| 1  | 636363636890 | 2279            |
| 2  | 636363636891 | 30             |
| 3  | 636363636893 | 356            |
| 4  | 6625839827   | 82              |
| 5  | 6625839828   | 32              |
| 6  | 6625839829   | 10              |

我试过 Postgres 这个查询：

INSERT INTO table-a (deviceSerial,time_difference) 
    SELECT
        deviceserial,
        SUM(DATE_PART('day', MAX(createdat)::timestamp - 
MIN(createdat)::timestamp) * 24 +
                DATE_PART('hour', MAX(createdat)::timestamp - 
MIN(createdat)::timestamp) * 60 + 
                DATE_PART('minute', MAX(createdat)::timestamp - MIN(createdat)::timestamp)) AS time_difference  
    FROM 
        table-b 
    GROUP BY 
        deviceserial;

但我得到一个错误。你能建议一个解决方案吗？

标签： sqlpostgresql

解决方案

我觉得你的问题有点难以理解。但是，如果我理解正确，您需要一个 UPSERT 命令——如果新设备不存在则插入一个新设备，否则会增加该timedifference列。基本思想是从一个独特的约束开始deviceSerial（如果你还没有）：

alter table b add constraint unq_b_deviceSerial unique (deviceSerial);

然后on conflict用于insert：

insert into b (deviceSerial, timedifference)
     select deviceSerial, timedifference
        from (<your query here>)
        on conflict (unq_b_deviceSerial) update
            set timedifference = b.timedifference + excluded.dimedifference

推荐阅读