CODEWITHSUNDEEP
c++databasepostgresqloptimizationlibpqxx
esavier
esavier

Reputation: 463

pqxx return id of just inserted row

i am using c++ 4.8 ( available 4.9) and pqxx driver ver. 4.0.1. postgresdb is latest stable.

My problem is all about complexity and resource balance:

I need to execute insert to database (and there is optionally pqxx::result) and id in that table id is based on nextval(table_seq_id)

Is is possible to get id of inserted row as a result? There is a workaround on this to ask db about currentvalue in sequence and just insert query with currentvalue+1 (or +n) but this will require to do "insert and ask" chain.

Db should be able to store more than 6K large requests /per.sec. so i would like to ask about id as infrequent as possible. Bulk insert is not an option.

Upvotes: 8

Views: 2224

Answers (1)

Mike Seymour
Mike Seymour

Reputation: 254701

As documented here, you can add a RETURNING clause to the INSERT query, to return values from the inserted row(s). They give an example similar to what you want, returning an ID:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

Upvotes: 8

Related Questions