修改Django AutoField起始值 [英] Modify Django AutoField start value

问题描述

我有和现有的数据库，我已经使用SQLAlchemy迁移到一个新的PostgreSQL数据库。

我已经移动了与以前相同的所有主键。现在我有表填充数据，但相关的序列从1开始。我有pk值存储1到2000.

现在，当我尝试保存Django的东西，我有

重复键值违反关于主键的唯一约束
。

如何修改序列起始值或避免这种情况？

我目前的解决方案是：

  conn = psycopg2.connect（...）table_name中的table_name的
 
 
 cursor = conn.cursor（）
 cursor.execute（
 SELECT setval（'％s_id_seq'，（SELECT COALESCE（MAX（id），0）+1 FROM％s））; 
％ ，table_name））
  

它适用于我，但我不喜欢它。

解决方案

在PostgreSQL中设置/重置序列的方法

（不一定要 max（id））。

• 有这个问题的简单方法。您可以使用 setval（） ：

    SELECT setval（'tbl_id_seq'）; 
    




• 然后，标准的SQL方式是ALTER SEQUENCE

    ALTER SEQUENCE myseq RESTART WITH 1; 
    




• 如果您想以除默认值1之外的数字重新启动序列：

    CREATE TABLE foo（id serial，a text）; - 创建序列foo_id_seq
   INSERT INTO foo（a）VALUES（'a'）; -  seq。以 - > 1 
   
   ALTER SEQUENCE foo_id_seq START WITH 10; - 不重新启动序列
   INSERT INTO foo（a）VALUES（'b'）; - > 2 
   
   ALTER SEQUENCE foo_id_seq RESTART; - 重新启动序列
   INSERT INTO foo（a）VALUES（'c'）; - >另外还有另一种方法，当你用 TRUNCATE ：
    TRUNCATE foo RESTART IDENTITY; 
    
  隐式执行 ALTER SEQUENCE foo_id_seq RESTART;  / p> 




I have and existing database, which I have migrated with SQLAlchemy to a new PostgreSQL database.

I moved all primary keys with the same values as before. Now I have tables filled with data, but the associated sequences starts from 1. I have pk values stored 1 to 2000.

Now, when I try to save something with Django, I have the

duplicate key value violates unique constraint regarding to the Primary Key.

How can I modify the sequence start values or escape this situation?
My current solution is:

conn = psycopg2.connect(...)
    for table_name in table_names:

        cursor = conn.cursor()
        cursor.execute("""
        SELECT setval('%s_id_seq', (SELECT COALESCE(MAX(id),0)+1 FROM %s));
        """% (table_name, table_name))

It works for me, but I don't like it.

解决方案

Ways to set / reset a sequence in PostgreSQL

(not necessarily to max(id)).

• There's the simple way you have in the question. You can set the sequence to start at an arbitrary number with setval():

  SELECT setval('tbl_id_seq');
  

• Then there's the standard SQL way with ALTER SEQUENCE doing the same:

  ALTER SEQUENCE myseq RESTART WITH 1;
  

• If you like to restart your sequences at numbers other than the default 1:

  CREATE TABLE foo(id serial, a text);      -- creates sequence "foo_id_seq"
  INSERT INTO foo(a) VALUES('a');           -- seq. starts with --> 1
  
  ALTER SEQUENCE foo_id_seq START WITH 10;  -- doesn't restart sequence
  INSERT INTO foo(a) VALUES('b');           --> 2
  
  ALTER SEQUENCE foo_id_seq RESTART;        -- restarts sequence
  INSERT INTO foo(a) VALUES('c');           --> 10
  

• And there is another way, when you empty a table with TRUNCATE:

  TRUNCATE foo RESTART IDENTITY;
  

  Implicitly executes ALTER SEQUENCE foo_id_seq RESTART;

这篇关于修改Django AutoField起始值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！