Postgresql uses sequences, and has a series of transaction safe functions for dealing with them. There's also a non-transactionally safe interface, for things like late night admin work :-)
The most common usage is with serial data types, which are just a macro for assigning a sequence to an integer field:
create table test (info text, id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
CREATE TABLE
\d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | test | table | marlowe
public | test_id_seq | sequence | marlowe
(9 rows)
-- The following is NON transactionally safe, i.e. prone to race conditions:
select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
test_id_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
(1 row)
For working with the sequences, you have three functions: setval, nextval, and currval:
currval() only works when the current session has actually called setval or nextval, since currval only has a safe meaning after one of those has been called. Note that currval will be different for two different transactions:
select currval('test_id_seq');
ERROR: test_id_seq.currval is not yet defined in this session
insert into test values ('abc');
INSERT 109704045 1
select currval('test_id_seq');
currval
2
(1 row)
If I go to another window, while this one is still open:
insert into test values ('def');
INSERT 109704046 1
marlowe=# select currval('test_id_seq');
currval
3
(1 row)
Back to the old window:
select currval('test_id_seq');
currval
2
(1 row)
So each session sees the last id that was inserted by it. Note that this method allows for massive parallel operation without bottlenecks.
.