Re: [sqlalchemy] Duplicate primary key on table migration

[Mike Bayer]

On Fri, Mar 23, 2018 at 7:41 PM, Colton Allen  wrote:
> I'm moving data from one table to another.  During this move I'm preserving
> the ID of the old table before dropping it.  However, by doing so the
> sequence gets out of whack and the database will no longer allow inserts to
> the trigger table.  What can I do to fix the broken sequence?  The id column
> is a UUID.
>
> # Migration
> select = sa.select([outcome.c.id, outcome.c.a, outcome.c.b])
> statement = sa.insert(trigger).from_select(['id', 'a', 'b'], select)
> connection.execute(statement)
>
> # Insert some time later
> statement = sa.insert(trigger).values(a=1, b=2)
> connection.execute(statement)  # duplicate key value violates unique
> constraint "trigger_pkey"


im confused by "UUID" here vs. "sequence", if you are referring to an
integer sequence you can ALTER those so that their starting number is
higher.

https://www.postgresql.org/docs/10/static/sql-altersequence.html

no idea if you're on Postgresql, just guessing

>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Duplicate primary key on table migration

[Colton Allen]

I'm moving data from one table to another.  During this move I'm preserving 
the ID of the old table before dropping it.  However, by doing so the 
sequence gets out of whack and the database will no longer allow inserts to 
the trigger table.  What can I do to fix the broken sequence?  The id 
column is a UUID.

# Migration
select = sa.select([outcome.c.id, outcome.c.a, outcome.c.b])
statement = sa.insert(trigger).from_select(['id', 'a', 'b'], select)
connection.execute(statement)

# Insert some time later
statement = sa.insert(trigger).values(a=1, b=2)
connection.execute(statement)  # duplicate key value violates unique 
constraint "trigger_pkey"

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.