[UPHPU] Postgres Insert ID

[Joseph Scott]

On Oct 22, 2008, at 5:30 PM, Sean Thayne wrote:

> I'm pretty sure that if this was the case before, it's definitely  
> fixed now.
> I'm actually a big fan of last_insert_id(), it saves me the time of  
> having
> to check the database for the sequence name and placing another call.
>
> With mysql I can do this
>
> mysqli_query(
> "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart',  
> 617000)"
> );
> $newId = mysqli_last_insert_id();
>
>
> Where in Postgres I need extra info
> pg_query(
> "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart',  
> 617000)"
> );
> $sequenceQuery = pg_query("SELECT curval(seq_city_id)");
> $result = pg_fetch_assoc($sequenceQuery);
> $newId = $result[0];
>
> Seems like extra work for no extra benefit, I've used mysql on large
> clusters, and the only thing your need to do is add a auto_increment  
> offset
> to avoid duplicate primary keys, but that's a one time deal. I know  
> I'm
> complaining over an extra two lines of code. But it just seems like  
> a waste
> of time.

First off, those queries are out of order.  You want to get the next  
value of the sequence first and then use that value in the insert.

As for why this is the case, in general sequences are much more  
efficient that auto_increment techniques.  It's very easy to make this  
separate object (sequence) very fast and efficient.  Embedding that  
feature in a table requires more work.

It's just different, not something to make a big deal over unless you  
are trying to write an app that supports both PostgreSQL and MySQL.   
In which case I recommend looking at libraries that specialize in that  
sort of thing, like adodb.


> It does sound like MySQL added this for their own database, and it's
> probally not part of the sql standard, but I think it would be nice  
> feature
> to add to all databases... Unless theirs some reason I'm missing....


--
Joseph Scott
joseph at randomnetworks.com
http://joseph.randomnetworks.com/