PostgreSQL false IntegrityError

Question

Couldn't find a solution. I now pass a list of tuples with the information to insert instead of extracting from the other table and the problem went away.

I am currently writing code that will be editing a table. I have several rows of information to insert but I keep getting IntegrityError on the fourth element, regardless of order.

Here is how I create the table:

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

Now, the segment_id must match another table, so it is not and cannot be sequencial. Here is the function:

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()

If I order by speed, it fails on the fifth instead of the fourth element. It seems to fail on lower IDs. This is the testing stage, so I have deleted and recreated the table multiple times, I KNOW there is no row in this table with the given ID.

I have read this question and this blog post but their solutions don't work because my IDs are not assigned sequentially or automatically.

My only thought at the moment is to remove the PRIMARY KEY constraint, but that is not ideal.

Output for reference:

(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

How many rows are there in `other_table`? – Clodoaldo Neto Feb 22 '17 at 16:19 — Clodoaldo Neto, Feb 22 '17 at 16:19

score 0 · Answer 1 · answered Feb 22 '17 at 16:23

0

Do it all at once:

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
)

answered Feb 22 '17 at 16:23

Clodoaldo Neto

118,695
26
233
260

I didn't try this solution because I saw it after I managed to get it working the other way. Thank you anyways, I appreciate the help. – gamda Feb 22 '17 at 19:57

PostgreSQL false IntegrityError

1 Answers1