Insert query for PostgreSQL In Drupal 7 with a developed custom module that insert data into a postgreSQL db, I use the following query :

$query = Database::getConnection('default', 'move')
  -&gt;query("INSERT INTO clients (client_ref,client_lastname,client_firstname) VALUES('E11366235','DUPOND') RETURNING client_id")
  -&gt;execute();


But the returned client_id is always 1.

If I set INSERT INTO clients (client_ref,client_lastname,client_firstname) VALUES('E11366235','DUPOND') RETURNING client_id in pgAdmin 3, the returning id is correct.

In both case data are inserted into database but Drupal doesn't return the good id.

Any explanation ?
According to all messages there are two ways to solve the problem :

1) db_insert

Using db_insert() to insert data and create a specific query for postgreSQL database to get the last id after insert :

// Default insert    
(...)-&gt;insert("...");

// Call this query to get last id only for pgSQL
(...)-&gt;query("SELECT currval('sequence')")-&gt;fetchAll();


You juste need to replace sequence by the key provided in your postgreSQL database.

You have to call currval right after insert to stay in the same context.

2) db_query

Rewriting the entire query with specific synthax for postgreSQL database to get the last id :

(...)-&gt;query("INSERT INTO {$table} ($fields) VALUES($value) RETURNING $id")-&gt;fetchField();


Conclusion

The first solution seems to be the best to avoid rewriting all db_insert() functions.