Golang pq sql driver: get credentials after bulk import
Using the pq sql driver for golang I am doing bulk imports as described in the pq docs . Is there a way to get the IDs of the created posts?
source to share
I am assuming that the id column you are referring to in your COPY statement is based on the sequential generation sequence created by this type of statement:
CREATE SEQUENCE my_serial_name;
This means that you can query the current id value like this:
SELECT currval('my_serial_name');
The current value of the id counter will be returned.
Answering any further doubts about this method:
- this operator is thread-local and completely isolated ,
- the above statement proves that this technique is useful in a multi-user environment and will produce the correct value unmodified by other insert queries executed on tables using this sequence
For further reading, please refer to the URLs that state that:
The sequence functions listed in Table 9-42 (currval included) provide simple, multi-user methods for obtaining sequential 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 ordinal assignment is necessary. You can construct an aimless assignment by using exclusive locks on the table that contains the counter; but this solution is more expensive than sequence objects, especially if many transactions require sequence numbers at the same time.
https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
To set the isolation level of a transaction transaction, use the SET TRANSACTION command.
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 sequential) are immediately reflected by all other transactions and are not rolled back if the transaction that caused the change is rolled back. See Section 9.16 and Section 8.1.4.
https://www.postgresql.org/docs/9.5/static/transaction-iso.html
I hope this answers your question. Postgresql is a great database and can be a very powerful tool to learn in detail. Good luck !: D
source to share