On Tue, March 6, 2007 08:13, Jim Stewart wrote: > I'm wondering about alternative ways to insert data. In the libpqxx > tests, > there are plenty of examples that insert e.g. a 2D array of strings, with > an > assumed field order. They all use pqxx::work::insert().
Confusion reigns. There is no pqxx::work::insert(), and the tests in fact exercise the entire API. There is also the tablewriter class, which optimizes bulk data insertion using the postgres COPY command. To specify an explicit list of columns, pass a sequence of column names to its constructor. I see now that the documentation never really explained this, so I'm adding a bit of text. A tablewriter is likely to be faster than a prepared statement if you wish to insert multiple rows into the same table in one go. > I set up a prepared statement to handle an insert, and it worked > fine..except > result.inserted_oid() returned 0. The row was actually inserted in the > DB, and result.affected_rows() returned 1. [...] > Not shown in the prepared statement is the "id" field, a serial. That's > the value I was hoping to get from result.inserted_oid(). Ah, no, it doesn't work like that. PostgreSQL has its own row identifiers, in a special column called oid that isn't shown when you "SELECT *". This extra column used to be present in all tables, but nowadays it's optional and disabled by default. If you do want the oid column, create your table with the WITH OIDS option. Then, when you insert a row, inserted_oid() should return its assigned oid. Be careful though: these oids are only 32 bits wide and *not* designed to deal with wraparound. > 2) If so, is there some reason it's not showing up in inserted_oid()? Remember that rows are not really objects: in theory they don't have identity, just values. There is no perfectly clear-cut way for the database system to know that you consider one of these values the row's "id". There could be any number of unique numeric sequences in the same table. So if you really want to hold on to an identifier for a row right from the very start, you may need to assign one yourself and enforce it with a unique constraint. Typically you only need to do that in one table, e.g. you might have a "user" table with a unique "email" field in addi _______________________________________________ Libpqxx-general mailing list Libpqxx-general@gborg.postgresql.org http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
