Creating Fixture Data for PostgreSQL with SqlAlchemy and Id's

I'm trying to inject sample data into my database, and have created a fixture file that builds my model, and then merges the instance into the database. I'm using merge, because I want to be able to re-run the fixture without recreating the database, or truncating all my tables.

However, upon inspection of my Postgres table's sequence on primary key, my_items_id_seq shows the following after injecting 10 items:

       Sequence "public.my_items_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | my_items_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | t

How can I increase the last_value开发者_如何学JAVA as I merge in fixture data?

[edit]

The outstanding issue here is that after injecting the data, I can't add items to the database without getting an IntegrityError from SqlAlchemy. This is due to PostgreSQL trying to insert my new instance with id 1.

---

The first part of the answer came through reading a link about Django and PostgreSQL, where the author similarly attempted to feed migration data with indexes.

His solution was to grab the highest index, and then execute the following query:

alter sequence profile_billingaddress_id_seq restart with {id};

Through this, I discovered the proper keyword to the PostgreSQL documentation on Sequence Manipulation Functions. The preferred way to do this is to issue this query:

select setval('my_items_id_seq', {id})

... where {id} is obviously a placeholder for a real integer. It also came to my attention that last_value is just the last response from next_value. This is important because using setval instead of the alter sequence business above ensures that nextval provides an unused index. I.e. when using setval where id=10, nextval will return 11. When using the alter sequence command, if id=10, then nextval will also return 10.

---

I had the same problem but was trying to insert fixtures for various different models.

The following little snippet from postgresql's wiki will do just what you want but for every sequence in your schema.

SELECT 'SELECT SETVAL(' ||
   quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
   ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
   quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

It looks a bit magical but does precisely what the accepted answer suggests.