Get Insert ID after pg_query()

Question

I am using php and postgres on a web system, I have a form that I am saving by using the Insert using pg_query, however, I would like to get the id of the new record after inserting, but I am not succeeding, or returns null or false. What I’ve already tried:

$resultado = pg_query($sql);
var_dump(pg_last_oid(pg_affected_rows($resultado)));


$resultado = pg_query($sql);
var_dump(pg_last_oid($resultado));


$row = pg_fetch_row($resultado);
var_dump($row[0]);


$row = pg_fetch_array($resultado, null, PGSQL_ASSOC);
var_dump($row[0]);

Nothing worked out, there’s a way to do it ?

Answer 1

I was able to get the last id by adding at the end of Insert (RETURNING id) and then I used this command

$ultimoID = pg_fetch_array($result,0)[0];

Full example:

$sqlInsert = "Insert into tabela (campo1) values ('teste') RETURNING id";
$result = pg_query($con, $sqlInsert);
$ultimoID = pg_fetch_array($result,0)[0];

Answer 2

There are two ways to do this, let’s go to the most recommended one in PHP which is using the method/function pg_last_oid

The error in your code is because you are passing the method/function value pg_affected_rows that only counts the number of affected lines and returns an integer.

The correct is, when using the method/function pg_last_oid, is to pass the Resource returned from the following functions. pg_query(), pg_query_params() or pg_execute(). Thus:

$result = pg_query($connection, "INSERT INTO `table` (name) VALUES ('Fulano');");

if ($result == false) {    
    die( pg_last_error() );
} else {
    $lastId = pg_last_oid($result);
}