Postgres increment SERIAL column per transaction

Question

If I understand SERIAL type correctly, it is auto-incremented if no value is specified for every INSERT statement. I was wondering if I can have auto-increment applied once per group of INSERTs or even per transactions.

E.g. If I do 3 inserts committing after each one I get 1,2,3 in the SERIAL column. I wanted to create single transaction that has 3 inserts and increment the row affected rows, ending up with 1,1,1 in the SERIAL column for those rows. If I were to do this again, I would get 2,2,2 not 4,5,6

Not possible with a serial column. You could create a "regular" sequence, and then query the sequence for nextval() at the start of your transaction and then use currval() in all inserts during that transaction. — user330315
– user330315, Commented Mar 31, 2015 at 14:28

tsnorri · Accepted Answer · 2015-03-31 23:27:25Z

You can use a sequence as described by @a_horse_with_no_name and automate the process with a function that provides the default value. The same value will be returned when called multiple times in a transaction.

CREATE SEQUENCE test_id_seq;

CREATE FUNCTION test_id_nextval() RETURNS INTEGER AS $$
DECLARE
    retval INTEGER;
BEGIN
    -- Try to fetch the current value.
    SELECT * FROM test_id_seq_temp LIMIT 1 INTO STRICT retval;
    RETURN retval;
EXCEPTION
    WHEN undefined_table THEN
        -- The function hadn't been called yet during this transaction.
        -- Create a temporary table for the next value of the sequence and return it.
        CREATE TEMPORARY TABLE test_id_seq_temp ON COMMIT DROP AS SELECT nextval('test_id_seq');
        SELECT * FROM test_id_seq_temp LIMIT 1 INTO STRICT retval;
        RETURN retval;
END;
$$ VOLATILE LANGUAGE plpgsql;

CREATE TABLE test (id INTEGER DEFAULT test_id_nextval());

Collectives™ on Stack Overflow

Postgres increment SERIAL column per transaction

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related