自动增加generate_series中变量的值

Question

我碰到需要自动增加generate_series中变量的值。根据业务逻辑的条件，模型中的字段标识符不会自动增加。

我决定尝试提前找到最大的id值，并在生成的范围内，尝试简单地将其增加1。但是我不知道如何正确地做这件事。

max_id = Companies.objects.aggregate(Max('id'))  # {'id__max': 1529367}
value = max_id['id__max']
cursor.execute(f'''
INSERT INTO test.companies(
id, name, city_id, revenue, labors
)
SELECT  {value} = {value}+1,   # This is what I want, but I don't know how to do it 
(
SELECT x[1 + ((random() * 25)::INT) % 3]
FROM (
SELECT '{{Google,Yandex,Amazon}}'::text[]
) AS z2(x)
WHERE SEQ = SEQ
),
(random() * 3000)::INT,
(random() * 3000)::INT,
(random() * 3000)::INT
FROM generate_series(1,10)  SEQ;
''')

所有这些都是我为Python Django做的工作的一部分。

Answer 1

在Python而不是SQL中运行从1到10的迭代是否有任何障碍?除此之外，我认为你在SQL语句中遗漏了VALUES。

max_id = Companies.objects.aggregate(Max('id'))  # {'id__max': 1529367}
value = max_id['id__max']

for addval in range(1,10):
cursor.execute(f'''
INSERT INTO test.companies(id, name, city_id, revenue, labors)
VALUES (''' + value+addval + ''',
(
SELECT x[1 + ((random() * 25)::INT) % 3]
FROM (
SELECT '{{Google,Yandex,Amazon}}'::text[]
) AS z2(x)
WHERE SEQ = ''' + addval + '''
),
(random() * 3000)::INT,
(random() * 3000)::INT,
(random() * 3000)::INT
)
'''

编辑如果你想用PostgreSQL完成这一切，为什么不从value+1开始你的generate_series呢?:

max_id = Companies.objects.aggregate(Max('id'))  # {'id__max': 1529367}
value = max_id['id__max']
cursor.execute(f'''
INSERT INTO test.companies(
id, name, city_id, revenue, labors
)
SELECT  SEQ,
SELECT x[1 + ((random() * 25)::INT) % 3]
FROM (
SELECT '{{Google,Yandex,Amazon}}'::text[]
) AS z2(x)
WHERE SEQ = SEQ
,
(random() * 3000)::INT,
(random() * 3000)::INT,
(random() * 3000)::INT
FROM generate_series(''' value + 1 ''',10)  SEQ;
''')