postgresql - PostgreSQL错误的IntegrityError

找不到解决方案。我现在传递一个包含要插入的信息的元组列表，而不是从另一个表中提取，问题就解决了。
我目前正在编写代码，将编辑一个表。我有几行要插入的信息，但是不管顺序如何，我总是在第四个元素上得到IntegrityError。
下面是创建表的方法：

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

现在，段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()

如果我按速度排序，它在第五个元素而不是第四个元素上失败。在低身份证上似乎失败了。这是测试阶段，因此我已经多次删除并重新创建了该表，我KNOW该表中没有具有给定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

最佳答案

一次全部完成：

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
)

关于postgresql - PostgreSQL错误的IntegrityError，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42396201/