Search code examples
postgresqllastinsertid

Last inserted row id in PostgreSQL

I am using postgresql database. And previous tables which are created by my seniors and they are not used sequence concept, they added data manually. Now i need to insert data with my java application. for that i need last inserted ID. I have no permission to add sequence.
please anybody help me.
thanks in advance...

Solution

  • You'd ideally ask for permission to create the needed sequences.

    If it's not an option for some bureaucratic reason you might manage to work around concurrency issues using advisory locks. Pseudo-code:

    loop
select id as last_id, pg_try_advisory_lock('yourtable'::regclass, id) as locked
from yourtable
order by id desc limit 1

 if not locked then sleep .01 else exit end if
end loop

new_id = last_id + 1
insert...

select pg_advisory_unlock('yourtable'::regclass, last_id)