Is there a portable way of resetting sequences on platforms where they are used? I am initializing tables (for unit tests) - which have an autoincrementing id column as primary key - by uploading data from fixture files on disk into a database. On SQLite everything works fine, but on Postgres the sequences get initialized to 1 and need to be set to reflect the id values which have been inserted, otherwise I get duplicate-key errors when inserting.
What database-independent mechanism can I use to do this? I couldn't find sufficient detail in the docs/on this list to help me figure it out. Here's a couple of posts I looked at: http://groups.google.com/group/sqlalchemy/msg/bd14c45a3cad801f to which Mike Bayer said, "after populating the tables with your CSV data, manually increment the sequence to the next available id." Yes, that's what I'd like to do, but without any direct execution of Postgres-specific SQL from my code. Following this post: http://groups.google.com/group/sqlalchemy/msg/c1942b1e38f282c9 which asks about how to operate on sequences, Mike Bayer makes the comment: "table reflection does get a value for sequence defaults in PG, and SQLA then knows how to execute the sequence. there is an issue specifically when the sequence name has been changed in that PG no longer provides consistent access to the sequence name (theres a trac ticket for that issue), but it works for the typical use case." I think my use case is pretty typical, but I could use a few pointers on how to get SQLA to execute select setval('XXX_id_seq', max(id)) from XXX I'm happy doing table and column reflection to decide when to make these calls, but I want to stay at the SQLA layer so that my code works unchanged on all mainstream databases. Thanks for any pointers, Vinay Sajip -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
