Migrating From MySQL To PostgreSQL In PHP

Replacing MySQL's Auto-increment

In MySQL, to create an auto-incrementing field called someID, you would do something like this:

create table myTable (
    someID      int             not null        auto_increment,
    name        varchar(255)    not null,
    primary key (someID)
);

In PostgreSQL, to get the same behaviour, you do:

create table myTable (
    someID      serial          not null,
    name        varchar(255)    not null,
    primary key (someID)
);

Using serial is basically a shortcut for the server to create an integer column, a sequence and a trigger. A sequence is a basically a function that returns an incremented number each time you call it, while a trigger is a function that is executed every time data is inserted or updated (in this case only inserts are relevant).

Tough to explain – it’s probably just as easy to read Serial Types in the PostgreSQL manual.

Finally, to fetch a newly inserted serial from a PostgreSQL table from PHP, you firstly need to call pg_last_oid(). This returns an OID (see the previous chapter), and you then need to select the serial column using the OID.

Here’s an example, using the SQL just above.

<?php
    $result = pg_query($link, "insert into myTable (name) values ('phpRiot')");
    $lastOid = pg_last_oid($link);
 
    $result = pg_query($link, "select someID from myTable where oid = " . $lastOid);
    $row = pg_fetch_row($result);
 
    $newId = $row->someID;
?>

It’s a little bit longer to do than in MySQL, but you’re probably using some kind of database abstraction anyway, into which you could automate this into a single function call.

• Next: Summary