Using db_merge if key is ID type serial

Question

I would like to know if there is any option for db_merge function to do UPSERT (update or insert) operation in table if key is "id" column of serial type (autoincrementing).

I need to insert new row if "id" is not defined, or update if "id" is specified. For now I have:

db_merge('t_project_proposal_writer')
   ->key(array('id' => $state['storage']['project']['proposal_id']))
   ->fields(array( .... ))->execute();

where $state['storage']['project']['proposal_id'] is null, because is not specified and it should create row with next id value. It is not working, returning:

Numeric value out of range: 7 ERROR: setval: value 0 is out of bounds for sequence "t_project_proposal_writer_id_seq"

It is working, when new id is manually specified, but then, how to get last inserted id of table? db_last_insert_id is not working in D7, only in D6.

Answer 1

No, there isn't any way to do that now (in 6 or 7). Simply put, that's not how Drupal was designed.

Inspect tables created by Drupal. See any increment here? No. It was design decision not to perform any id assignment in database, but leave it all to code. If you go against that decision, you need to implement your own solutions, using more basic db_select, if, db_update and db_insert. Or prepare your own query using db_query.

Answer 2

This sounds like a good fit for drupal_write_record. Something like this:

$result = drupal_write_record('t_project_proposal_writer', $fields, !empty($state['storage']['project']['proposal_id']) ? 'id' : NULL);

if ($result === FALSE) {
  // Failure.
}
elseif ($result == SAVED_NEW) {
  // A new record was saved.
}
elseif ($result == SAVED_UPDATED) {
  // A record was updated.
}