Reset BIGSERIAL counter on TRUNCATE_TABLE in PostgreSQL

[raderio]

CREATE TABLE accounts (id BIGSERIAL NOT NULL PRIMARY KEY);

@DatabaseTearDown(value = "clear.xml", type = TRUNCATE_TABLE)

<dataset>
    <accounts/>
</dataset>

@ExpectedDatabase

<dataset>
    <accounts id="1" />
</dataset>

When I run first time everything is ok. But on second time I have an error

junit.framework.ComparisonFailure: value (table=accounts, row=0, col=id) expected:<[1]> but was:<[2]>

[raderio]

I can reset it by running TRUNCATE TABLE accounts RESTART IDENTITY;

How to make dbunit to do that?

[lvv83]

Hi.

On my case dbunit inserts left a table with sequence in wrong state, because dbunit doesn't call Postgre nextval() function. And after this we can't use any tools or libs such as PgAdmin or Hibernate with identity generation.

Solution based on idea from this post. We need to call setval() function after dbunit inserts.

Steps:

• extend AbstractOperation class to run a custom SQL operator.
• create custom lookup class to integrate operation implementation with springtestdbunit.
• pass lookup class to the @DbUnitConfiguration

Example gist I think you can easily adapt it for your scenario.