Postgres, coldfusion and selecting the id just inserted

Posted At : April 28, 2007 12:00 AM | Posted By : Jon Hirschi
Related Categories: Coldfusion Programming

This week and for the next however long amount of time, I'm working with the postgres database. the last time i worked with postgres as a database was back in 1998 when I was learning to do web programming, and working with a database in perl even. At that time, i remember it was a complete pain to work with a database, much less postgres - and in perl even. Since then, I think database support has gotten better all around AND I was most pleasantly surprised to find that postgres was much easier to work with this time around.

I'm sure that a lot of that has to do with the fact that I've got much more database knowledge than when i was starting out, and you know, frankly, the databases have gotten even better. Now, I have no wish to offend any mysql-ites out there, but I have to say that working with postgres has been sooooo much easier than working with mysql..

Anyway, this all brings me back to the topic of my post. I was looking around for a way to get the record id of an entry that is just inserted into the database, when using the auto indexing columns. The traditional way of:

<cfquery name=myinsert datasource=mysource>
insert queyr stuff
</cfquery>
<cfquery name=maxid datasource=mysource>
select max(id) from insertedtable
</cfquery>

It just doesn't work, and it suffers from that ill fated "WHAT IF?". You know the one... what if two people try to insert at the same time.. then you get mixed up ids.. and that's a pretty bad thing to do... yup... so true.. I even went as far as to go the next step and do the whole around the queries, though we know that it doesn't work either.

What I used to do with sql server was that we'd use the old @@identity hook and get the id that way... always seemed to be much cleaner, and you didn't have to worry about the transaction or locking, or any of that. With postgres and mysql it's pretty easy to do to, with post gres, you can just append "returning id" (or any other columns you want to return) to the insert query. Mysql has a function that you can call last_insert_id() (or something like that - sorry if i got it wrong mysql-ites).

I ran into one post out there that suggested creating a rule in the database so that *every* insert returned the id.

CREATE OR REPLACE RULE get_cars_id_seq AS ON INSERT TO cars do SELECT currval('cars_id_seq'::text) AS id;

However that doesn't work in coldfusion. for some reason, the value is returned, and in debugging, you can see that the the row id is returned (by the presence of a row in the results) but suddenly, the variable that should hold the query you just executed, has gone missing.

so for example, say you have a table (cars[id,carname]):

If you put the previous rule on the table and then try to insert a carname:

<cfquery name="myinsert" datasource="mysource">
insert into cars (carname) values ('impala')
</cfquery>

what you find is that suddenly, the queryname is undefined and throws an undefined variable error, even though a query was executed and returned rows. I have no clue why coldfusion would be doing that.

So, at the end of the long story, what can we say? use "returning id" to get the last inserted id, not the rule as is used in other spots. Now there are definately other ways available for managing the whole insert coorelation issue, but this solution is quite a bit easier than trying to manage it with double queries.