I am using an ODBC connection to a PostGres database, that has various tables that use SERIAL types to implement automatically incrementing primary keys.
My problem is that I have a dataset containing tables that I have turned AutoIncrement on, and set the seed and step to -1. My update statement works a treat, and any new rows are added sequentially to the end of the table. However, I need a way of immediately returning a new rows primary key, in order to keep the dataset up to date.
I know that in SQL Server you can use the @@IDENTITY feature, to immediately return the last used value. Is there something similar in PostGres?
I have tried adding a 'SELECT CURRVAR('public.equipmenttype_equipmenttypeid_seq') just after my insert statement (which is the SQL that you use to find out the current sequence id in PostGres), but I am not sure of the ODBC syntax needed to get this information into a parameter (I assume I would need to use a ParameterDirection.ReturnValue).
This problem has been vexing me for a few days now. Any ideas or a better way of handling autoincrementing keys would be gladly received.