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.
$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.