PostgreSQL Why sequence increases only once with two "nextval"

Question

I am implementing a board with a comment function.

I created a table like this with nextval

create table tr_board
(
    b_id varchar(20) not null default nextval('seq_board'),
    title varchar(256),
    content varchar(500),
    user_id varchar(30),
    update_date timestamp not null,
    is_complete varchar(1) default 0,
    is_private varchar(1) default 0
);

and this is my Insert sql.

    <insert id="addBoard" parameterType="java.util.HashMap">
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            select nextval('seq_board') as id;
        </selectKey>

        insert into tr_board
            (
                b_id,
                title,
                content,
                user_id,
                is_private,
                update_date
            )
        values
            (
                #{id},
                #{title},
                #{content},
                #{user_id},
                #{is_private},
                current_timestamp at time zone 'utc'
            )
    </insert>

I used nextval in the "default" of "create" and "insert".
So I thought the sequence would increase twice.
But Why is the sequence increasing only once, even though a total of two nextval exists?

I would appreciate your reply.

Thom Brown · Accepted Answer · 2020-05-20 03:04:14Z

1

The nextval() on the column default only gets called if a value isn't provided, or you explicitly ask for the default to be used. You are getting a sequence value, and then passing that as a value to the b_id column, so it doesn't try to get the next value in the sequence because no default is needed. It would only call it twice if you got the value, and then did nothing with it, requiring the default to be used.

answered May 20, 2020 at 3:04

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Why sequence increases only once with two "nextval"

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