last_insert_id or currval

2004 Aug 5 at 06:55 » Tagged as :mysql, postgresql,

When you are inserting data into a table that has a generated id you often need to know what the generated id was. With mysql it's real easy, just use the last_insert_id() function. When you enter data into a mysql table that has an auto_increment field you can then make use of this method call retrieve the identifier that was generated. If you happen to be using PHP the job is even easier  because you can retrieve this number mysql_insert_id() function of PHP without needing an extra query. At first glance it's counterpart in postgres seems to be the currval() function. But appearences can be deceptive. You don't need to pass in any parameters at all to last_insert_id() or the mysql_insert_id() ;  the database always returns the id associated with the last executed insert query for that connection. currval on the other hand takes the name of the related sequence as a parameter. So it seems the  only way to determine the insert id in pg is by inspecting the database meta data - a cumbersome task at the best of times; it takes quite a bit of effort and querying to get the task done. On the other hand as a programmer you already know the relationship between the sequence and the column so it's a simple matter to create your own function that can return the id for you. In fact it's a mere 5 lines of code.

CREATE FUNCTION add_user(char,char,integer) RETURNS BIGINT AS '
   INSERT INTO users(userName,userPassword,userStatus) VALUES($1,$2,$3);
   SELECT CURRVAL(''users_userid_seq'');
' LANGUAGE SQL;

Instead of executing an Insert query as you might have done with mysql you do a SELECT when you want to add data to your table. Sounds counter intuitive? See this example:

SELECT add_user('raditha'.'123',1);

The select is actually using a stored function that does an insert! As with any other select you are presented with a resultset and it contains the value that was last generated by the sequence for the current connection to the data Interestingly when talking to an MS sql server with JDBC you always get back a result set even with an INSERT query if the insert results in the auto increment field been updated. You can do the same by carrying out a select on @@id Follow Up: see the mysql 2 pgsql migration guide