postgresql：如何获取用批量copy_from插入的行的主键？ [英] postgresql: how to get primary keys of rows inserted with a bulk copy_from?

问题描述

目标是这样的：我有一组要放入表 A 的值，还有一组要放入表 B 。进入 A 中的 B 参考值的值（通过外键），因此在插入<$ c $之后c> A 值我需要知道在插入 B 值时如何引用它们。我需要尽快。

The goal is this: I have a set of values to go into table A, and a set of values to go into table B. The values going into B reference values in A (via a foreign key), so after inserting the A values I need to know how to reference them when inserting the B values. I need this to be as fast as possible.

我插入了 B 值，并从以下位置进行了批量复制：

I made the B values insert with a bulk copy from:

def bulk_insert_copyfrom(cursor, table_name, field_names, values):
    if not values: return

    print "bulk copy from prepare..."
    str_vals = "\n".join("\t".join(adapt(val).getquoted() for val in cur_vals) for cur_vals in values)
    strf = StringIO(str_vals)
    print "bulk copy from execute..."
    cursor.copy_from(strf, table_name, columns=tuple(field_names))

这比执行 INSERT VALUES ... RETURNING id 查询。我想对 A 值执行相同的操作，但是我需要知道插入的 id

This was far faster than doing an INSERT VALUES ... RETURNING id query. I'd like to do the same for the A values, but I need to know the ids of the inserted rows.

是否可以通过这种方式执行批量复制，但是要获取 id 字段（主键）插入的行中，这样我知道哪个 id 与哪个 value 相关联？

Is there any way to execute a bulk copy from in this fashion, but to get the id field (primary key) of the rows that are inserted, such that I know which id associates with which value?

如果没有，实现我的目标的最佳方法是什么？

If not, what would the best way to accomplish my goal?

编辑：根据请求提供示例数据：

Sample data on request:

a_val1 = [1, 2, 3]
a_val2 = [4, 5, 6]
a_vals = [a_val1, a_val2]

b_val1 = [a_val2, 5, 6, 7]
b_val2 = [a_val1, 100, 200, 300]
b_val3 = [a_val2, 9, 14, 6]
b_vals = [b_val1, b_val2, b_val3]

我要插入 a_vals ，然后使用外键而不是对列表对象的引用插入 b_vals 。

I want to insert the a_vals, then insert the b_vals, using foreign keys instead of references to the list objects.

推荐答案

自行生成ID。

1. BEGIN交易


2. 锁定表a


3. 调用nextval（）-这是您的第一个ID


4. 生成您拥有ID的COPY


5. 与表b相同


6. 调用setval（），最终ID + 1


7. COMMIT事务

1. BEGIN transaction
2. Lock table a
3. call nextval() - that's your first ID
4. generate your COPY with IDs in place
5. same for table b
6. call setval() with your final ID + 1
7. COMMIT transaction

在第2步中，您可能也想锁定序列的关系。如果代码调用nextval（）并将该ID存储在它使用时的某个地方，则它可能已经在使用了。

At step 2 you probably want to lock the sequence's relation too. If code calls nextval() and stashes that ID somewhere it might be already in use by the time it uses it.

有点不合实际的事实：存在一个缓存 设置，如果您有很多后端要进行大量插入操作，则可以设置。

Slightly off-topic fact: there is a "cache" setting that you can set if you have lots of backends doing lots of inserts. That increments the counter in blocks.

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

这篇关于postgresql：如何获取用批量copy_from插入的行的主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！