PostgreSQL 错误的 IntegrityError

Question

找不到解决方案。我现在传递一个元组列表，其中包含要插入的信息而不是从其他表中提取的信息，问题就消失了。

我目前正在编写将要编辑表格的代码。我有几行信息要插入，但无论顺序如何，我总是在第四个元素上收到 IntegrityError。

这是我创建表格的方式:

CREATE TABLE segment_speed_live_layer(segment_id INTEGER PRIMARY KEY,
                                      geom GEOMETRY(LINESTRING, 4326), 
                                      speed INTEGER);

现在，segment_id 必须匹配另一个表，所以它不是也不能是连续的。这是函数:

def update_layer():
    segment_cursor = connection.cursor()
    segment_query = 'SELECT segment_id, speed FROM segment_speed_live ORDER BY segment_id'
    exists_cursor = connection.cursor()
    exists_query = 'SELECT EXISTS(SELECT 1 FROM segment_speed_live_layer WHERE segment_id=%s)'
    insert_cursor = connection.cursor()
    insert_query = """INSERT INTO segment_speed_live_layer(segment_id, geom, speed)
                      SELECT %(segment_id)s, geom_way, %(speed)s
                      FROM other_table
                      WHERE segment_id=%(segment_id)s"""
    update_query = 'UPDATE segment_speed_live_layer SET speed=%(speed)s WHERE segment_id=%(segment_id)s'
    segment_cursor.execute(segment_query)
    for row in segment_cursor:
        segment_id, speed = row
        exists_cursor.execute(exists_query, (segment_id, ))
        exists = exists_cursor.fetchone()[0]
        query = update_query if exists else insert_query
        print(segment_id, speed, exists)
        print(insert_cursor.mogrify(query, {'segment_id': segment_id, 'speed': speed}))
        insert_cursor.execute(query, {'segment_id': segment_id, 'speed': speed})
        print(insert_cursor.statusmessage)
    connection.commit()

如果我按速度排序，它会在第五个而不是第四个元素上失败。它似乎在较低的 ID 上失败。这是测试阶段，所以我多次删除并重新创建了该表，我知道该表中没有给定 ID 的行。

我已阅读 this question和 this blog post但他们的解决方案不起作用，因为我的 ID 不是按顺序或自动分配的。

目前我唯一的想法是删除 PRIMARY KEY 约束，但这并不理想。

供引用的输出:

(243, 69, False)
INSERT INTO segment_speed_live_layer(segment_id, geom, speed)
                      SELECT 243, geom_way, 69
                      FROM other_table
                      WHERE other_table.segment_id=243
INSERT 0 1
(680, 9, False)
INSERT INTO segment_speed_live_layer(segment_id, geom, speed)
                      SELECT 680, geom_way, 9
                      FROM other_table
                      WHERE other_table.segment_id=680
INSERT 0 1
(11599, 42, False)
INSERT INTO segment_speed_live_layer(segment_id, geom, speed)
                      SELECT 11599, geom_way, 42
                      FROM other_table
                      WHERE other_table.segment_id=11599
INSERT 0 1
(16399, 40, False)
INSERT INTO segment_speed_live_layer(segment_id, geom, speed)
                      SELECT 16399, geom_way, 40
                      FROM other_table
                      WHERE other_table.segment_id=16399

Answer 1

一次性完成:

with u as (
    update segment_speed_live_layer ssll
    set speed = ssl.speed
    from segment_speed_live ssl
    where ssl.segment_id = ssll.segment_id
)
insert into segment_speed_live_layer (segment_id, geom, speed)
select segment_id, geom_way, speed
from
    other_table ot
    cross join
    segment_speed_live ssl
where not exists (
    select 1
    from segment_speed_live_layer
    where segment_id = ssl.segment_id
)