Bulk insert creating duplicate primary-keys

[Colton Allen]

I'm using Postgres and I am getting a duplicate primary key error when attempting to insert from a query.  My primary key is a UUID type.

statement = insert(my_table).from_select(['a', 'b'], select([sometable.c.a, sometable.c.b])
session.execute(statement)
session.commit()


Error: "DETAIL:  Key (id)=(f6bdf0e7-f2af-4f29-8122-5320e1ab428e) already exists."

This query runs successfully when the select on finds one row.  If there are more it fails.  Is there a way to instruct the query to generate a UUID for each row found?

[Colton Allen]

Also, I should mention the ID is not explicitly mentioned in the select query.  I am relying on the column's "default" argument to supply the UUID.

Also also, I made a typo.  It should read "when the select only finds one row".

[Mike Bayer]

what's the default on the column?  seems like that's the problem here.    if this is an in-SQLAlchemy default, that won't work, you need to generate the uuids on the database side if you are using a SELECT.   The functions are available through an extension on PG, there's background here: https://dba.stackexchange.com/a/122624/81161 on setting it all up.

if you dont want to set the DEFAULT on the column, you can run it in the SELECT using:

SELECT([func.uuid_generate_v1(), sometable.c.a, sometable.c.b])

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b5856820-7fc5-43ae-9633-24578626aa1a%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.