获取错误“重复键值违反唯一约束postgres”

Question

我有这样的桌子：

p_id | store |      createdat      | device  | deviceserial | application
------+-------+---------------------+---------+--------------+-------------
      | z10   | 2020-09-02 08:02:39 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 08:08:18 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 08:10:10 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 08:20:10 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 10:40:11 | IOS     | 6625839827   | app-b
      | z10   | 2020-09-02 10:45:11 | IOS     | 6625839827   | app-b
      | z10   | 2020-09-02 10:50:11 | IOS     | 6625839827   | app-b
      | z11   | 2020-09-02 08:47:10 | Android | 636363636891 | app-a
      | z11   | 2020-09-02 08:55:10 | Android | 636363636891 | app-a
      | z11   | 2020-09-02 08:59:10 | Android | 636363636891 | app-a
      | z11   | 2020-09-02 13:01:11 | IOS     | 6625839828   | app-b
      | z11   | 2020-09-02 13:15:11 | IOS     | 6625839828   | app-b
      | z10   | 2020-09-02 12:03:10 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 12:09:10 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 12:12:10 | Android | 636363636890 | app-a
      | z10   | 2020-09-02 15:15:11 | IOS     | 6625839827   | app-b
      | z10   | 2020-09-02 15:20:11 | IOS     | 6625839827   | app-b
      | z11   | 2020-09-02 10:25:10 | Android | 636363636891 | app-a
      | z11   | 2020-09-02 10:35:10 | Android | 636363636891 | app-a

我试图插入一些查询到另一个表（device_usage_test1）。这是我的表：

create table if not exists device_usage_test1(id SERIAL,deviceserial VARCHAR(50) UNIQUE,device VARCHAR(50),deviceusage DOUBLE PRECISION)

这是我的插入命令：

insert into device_usage_test1(deviceserial,device,deviceusage) select deviceserial,device,sum(deviceusage) as deviceusage
from (
    select deviceserial,device,
         
     extract(epoch from (max(createdat)::timestamp - min(createdat)::timestamp)) as deviceusage,
     date_trunc('hour', createdat) +
     (((date_part('minute', createdat)::integer / 10::integer) * 10::integer)|| ' minutes')::interval AS hr
    FROM datatable  
    group by deviceserial,hr,device
) t
group by deviceserial,device;

稍后，我将在device_usage_test1上创建一个插入查询。所以我的设备序列必须是唯一的。但是当我尝试用deviceserial (unique)插入时。它显示错误:< code >错误:重复的键值违反了唯一约束“device _ usage _ device serial _ key”

< code >详细信息:密钥(device serial)=(6363636890)已经存在。

Answer 1

你有数据问题。您的选择正在返回至少2个具有相同设备序列号(1004573GT7)的设备。尝试运行以下命令:

select * 
  from (select deviceserial
              ,device       
              ,extract(epoch from (max(createdat)::timestamp - min(createdat)::timestamp)) as deviceusage
              ,date_trunc('hour', createdat) +
               (((date_part('minute', createdat)::integer / 10::integer) * 10::integer)|| ' minutes')::interval AS hr
         from datatable where createdat > now() - interval '1 day' 
        group by deviceserial,hr,device
       ) s
having count(deviceserial)>1
order by deviceserial;

注意：没有给出表定义或示例数据，上述未测试。

第二:< br >不能有一个唯一的约束设备序列，同时又有多个设备用于一个设备序列。根据定义，每个deviceserail有多个设备在deviceserail上并不是唯一的。相反，您可以在两者(deviceserail，device)上定义您的唯一密钥。

drop table device_usage_test1; 
create table device_usage_test1(id serial
                               ,deviceserial varchar(50) 
                               ,device varchar(50)
                               ,deviceusage double precision
                               ,constraint device_usage_test1_pk 
                                           primary key (id)
                               ,constraint device_usage_test1_bk
                                           unique (deviceserial,device)
                               );   

然而，这允许您的初始坏数据同时具有“android”和“Android”共存。您可能希望限制设备的大写。例如:

alter table device_usage_test1
        add constraint device_initcap_check 
            check (device = initcap(device));