mysql_insert_id alternative for postgresql

by

From the PostgreSQL point of view, in pseudo-code:

 * $insert_id = INSERT...RETURNING foo_id;-- only works for PostgreSQL >= 8.2. 

 * INSERT...; $insert_id = SELECT lastval(); -- works for PostgreSQL >= 8.1

 * $insert_id = SELECT nextval('foo_seq'); INSERT INTO table (foo...) values ($insert_id...) for older PostgreSQL (and newer PostgreSQL)

pg_last_oid() only works where you have OIDs. OIDs have been off by default since PostgreSQL 8.1.

So, depending on which PostgreSQL version you have, you should pick one of the above method. Ideally, of course, use a database abstraction library which abstracts away the above. Otherwise, in low level code, it looks like:

Method one: INSERT… RETURNING

// yes, we're not using pg_insert()
$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_row($result);
$insert_id = $insert_row[0];

Method two: INSERT; lastval()

$result = pg_execute($db, "INSERT INTO foo (bar) values (123);");
$insert_query = pg_query("SELECT lastval();");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];

Method three: nextval(); INSERT

$insert_query = pg_query($db, "SELECT nextval('foo_seq');");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];
$result = pg_execute($db, "INSERT INTO foo (foo_id, bar) VALUES ($insert_id, 123);");

The safest bet would be the third method, but it’s unwieldy. The cleanest is the first, but you’d need to run a recent PostgreSQL. Most db abstraction libraries don’t yet use the first method though.

More Related Contents:

  1. Cannot simply use PostgreSQL table name (“relation does not exist”)
  2. Best way to get result count before LIMIT was applied
  3. Simple Postgresql Statement – column name does not exists
  4. Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL
  5. Get last insert id after a prepared insert with PDO
  6. pg_query result contains strings instead of integer, numeric
  7. Enabling PostgreSQL support in PHP on Mac OS X
  8. How to pass custom type array to Postgres function
  9. Can’t decrypt using pgcrypto from AES-256-CBC but AES-128-CBC is OK
  10. How to bind SQL variables in PHP?
  11. How do I enable php to work with postgresql?
  12. Setting a connect timeout with PDO
  13. How to prevent PDO from interpreting a question mark as a placeholder?
  14. Pass array literal to PostgreSQL function
  15. Postgres now() timestamp doesn’t change, when script works
  16. Convert PostgreSQL array to PHP array
  17. Different timezone_types on DateTime object
  18. PHP doesn't recognize mkdir
  19. If statement for variable ending in certain letters [closed]
  20. Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query [duplicate]
  21. Insert array into MySQL database with PHP
  22. Using $_POST to get select option value from HTML
  23. PHP Fatal error: Cannot redeclare class
  24. Get random item from array [duplicate]
  25. How to find out where a function is defined?
  26. Sending images from Canvas elements using Ajax and PHP $_FILES
  27. Get current URL path with query string in PHP [duplicate]
  28. Regex for checking if a string has mismatched parentheses?
  29. Set value of single object in multidimensional array in twig template
  30. SEO Friendly URL

Leave a Comment