February 8, 2005
The best solution to catch that mysterious serial
I have been using postgresql for a while, and using serial (autoincrement) is one of must use in many tables (about 70%), sometimes we need the serial to create a logical id that should enumerate the records, or juste to get a virtual id non-related with the data, sometimes we need to get back that record instantly inserted into the table, so we have to get get the select currentval('seq_name'), then to insert it manually into the table, oh , I know tha's really jerk, and you can't claim it after inserting, because in a multi-user/connection world, that last serial would be lost and you'll certainly hit false values , so ...I have finally found the best solutions that fits all my problem :
insert into test values(default);SELECT id FROM test ORDER BY id DESC LIMIT 1
When executing that query in the same line you'll get the auto serial id beeing insreterd withing the insert query and that's all folks !
Trackback
You can ping this entry by using http://phpmagazine.net/cgi-bin/pub/mt-tb.cgi/4298 .