Golang PQ SQL驱动程序：批量导入后获取记录ID

Question

Using the pq sql driver for golang, I'm the doing a bulk import as described in the pq docs. Is there a way I can get the ids of the created records?

Answer 1

I assume that the id column that you are referring to in your COPY statement is based on a serial generation sequence as created by this type of statement:

CREATE SEQUENCE my_serial_name;

This means that you can query the current value of the id like this:

SELECT currval('my_serial_name');

Which will return the current value of the id counter.

As answering any further doubts about this method:

• this statement is local to the thread and fully isolated,
• the above statement proves this method to be useful in a multi user environment and will produce correct value not modified by other insert queries performed on tables using this sequence

For further reading please refer to urls stating that:

The sequence functions, listed in Table 9-42 (currval included), provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

https://www.postgresql.org/docs/9.3/static/functions-sequence.html

Because nextval and setval calls are never rolled back, sequence objects cannot be used if "gapless" assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

To set the transaction isolation level of a transaction, use the command SET TRANSACTION.

Important: Some PostgreSQL data types and functions have special rules regarding transactional behavior. In particular, changes made to a sequence (and therefore the counter of a column declared using serial) are immediately visible to all other transactions and are not rolled back if the transaction that made the changes aborts. See Section 9.16 and Section 8.1.4.

https://www.postgresql.org/docs/9.5/static/transaction-iso.html

I hope that this answers your question. Postgresql is a great database and can become a very powerful tool when mastered in details. Good luck! :D