主页 / dba / 问题 / 265021
Accepted
Pavanraotk
Asked: 2020-04-15 21:36:57 +0800 CST

对现有表的约束失败

  • 772

我想将以下约束添加到现有表中：

ALTER TABLE ONLY daily_summary_202004 
  ADD CONSTRAINT odo_no_overlapping_202004 
  EXCLUDE USING gist (((vehicle_gid)::text) WITH =, numrange(odo_start, odo_end) WITH &&) 
WHERE (start_date >= '2020-04-01' 
   AND start_date < ' 2020-05-01' 
   AND active) ;

但是表中很少有条目在生产中没有达到约束。

是否可以编写一个 SQL 查询来获取所有错误行，以便在添加约束之前删除它们？

表的 DDL

create table if not exists ifta.daily_summary_202004
(
    id                 uuid           default uuid_generate_v4() not null primary key,
    distance           numeric(10, 3) default 0                  not null,
    fuel_code          varchar(128)                              not null,
    state              varchar                                   not null,
    odo_start          numeric(10, 3)                            not null,
    odo_end            numeric(10, 3),
    start_date         timestamp                                 not null,
    end_date           timestamp,
    vehicle_gid        uuid
);

本质上，不应有重叠的 odo_start 和 odo_end 条目，因此存在约束。但是由于一个现有的错误，我们有一些重叠的条目，我想找到它们并摆脱它们。

postgresql-9.6

1 个回答

  1. Best Answer

    我认为先前给出的查询已关闭您需要的内容：

    select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01' 
  AND ds1.start_date < date '2020-05-01' 
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) &&  
                        numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );

    它选择具有给定约束的表中不存在的行。但是它不能给出应该被拒绝的那些：它只给出候选人。你需要选择。

    如果我用一些数据进行模拟。我得到：

    select * from daily_summary_202004 ;
 id | vehicle_gid | odo_start | odo_end |     start_date      | active 
----+-------------+-----------+---------+---------------------+--------
  3 |          10 |        10 |      20 | 2020-04-15 00:00:00 | t
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(3 rows)

---
select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01' 
  AND ds1.start_date < date '2020-05-01' 
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) && numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );
 id | vehicle_gid | odo_start | odo_end |     start_date      | active 
----+-------------+-----------+---------+---------------------+--------
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(2 rows)

alter table only daily_summary_202004  
add constraint odo_no_overlapping
exclude using gist
   (
   ((vehicle_gid)::text) WITH =, 
     numrange(odo_start, odo_end) WITH &&) 
     WHERE (start_date >= '2020-04-01' 
      AND start_date < ' 2020-05-01' 
    AND active
) ;
psql:tc.sql:39: ERROR:  could not create exclusion constraint "odo_no_overlapping"
DETAIL:  Key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [3,4)) conflicts with key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [2,6)).

    此处 PostgreSQL 报告涉及的行，但您还必须选择保留哪一个和拒绝哪一个。

    • 1

相关问题