PostgreSQL Why sequence increases only once with two “nextval”

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.

Answer

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.