Mícéal Gallagher in Store procedure 2 minutes

PostgreSQL - Get the serial ID of a newly created record

When you assign an Id to an inserted record things are great! When PostgreSQL assigns the Id, things get a little complex, especially if you want to immediately use the id. I want to add a record to a table called blah and immediately store the id in a junction table. Using PgAdmin I discovered that the name of the blah table sequence is blah_id_seq. pgadminsequence Once I have inserted a record into the blah table I can use the CURRVAL function to get the current value of the sequence like so:

pgsql-- Insert the record which will automatically assigned an Id INSERT INTO blah (blabber) VALUES ('Noice!'); -- Create a junction table record using CURRVAL to get the Id of the created record INSERT INTO blah_junction_table (blah_id) VALUES (SELECT CURRVAL('blah_id_seq'));

And that’s it! References: http://sqlines.com/postgresql/datatypes/serial http://www.postgresql.org/docs/current/interactive/functions-sequence.html