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.
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