CODEWITHSUNDEEP
javasqlpostgresqlliquibase
sandris
sandris

Reputation: 1538

multiple insert with sequence fails in Postgres

There is one insert query with multiple values (about 18k):

INSERT INTO ENTRIES (ID, USER_ID) 
VALUES (nextval('my_seq'), '233'),(nextval('my_seq'), '233');

Which is fired from liquibase changeset in java application:

JdbcConnection connection = (JdbcConnection) database.getConnection();

        ResultSet resultSet = connection.prepareStatement("SELECT ID FROM USERS").executeQuery();

        List<String> values = new ArrayList<>();

        while (resultSet.next()) {
            Long userId = resultSet.getLong(1);

            for (int i = 0; i < 3; i++) {
                values.add("(nextval('my_seq'), '" + userId + "')");
            }
        }

        String sql = "INSERT INTO ENTRIES (ID, USER_ID) VALUES " + join(values, ",") + ";";

        connection.createStatement().execute(sql);

When it is executed against h2 database, everything is fine, but when I am running that against postgres the following exception is being thrown:

duplicate key value violates unique constraint "entries_pkey"

Like id from sequance is being reused.

When running sql in pgAdmin everything is ok, but from application it fails on the first insert.

My first thought is that maybe sql should be executed somehow differently.

Any thoughts on this?

Here is table description:

  CREATE TABLE entries
(
  id bigint NOT NULL,
  user_id bigint,
  CONSTRAINT entries_pkey PRIMARY KEY (id),
  CONSTRAINT fk_e3udjwux3ly7lu31huish0f82 FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE entries
  OWNER TO app;

Upvotes: 0

Views: 1763

Answers (3)

sandris
sandris

Reputation: 1538

Code was correct, the problem was size of the sequence - it was too small for 18k insert.

Upvotes: 0

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95602

As a rule of thumb, if your dbms can do something automatically, you shouldn't try to do it yourself.

CREATE TABLE entries
(
  -- bigserial provides automatic sequence numbers under
  -- dbms control.
  id bigserial NOT NULL,
  user_id bigint,
  CONSTRAINT entries_pkey PRIMARY KEY (id),
  CONSTRAINT fk_e3udjwux3ly7lu31huish0f82 FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Insert into this table without referring to "id" at all.

INSERT INTO ENTRIES (USER_ID) 
VALUES ('233'),('233');

Or use the "default" value.

INSERT INTO ENTRIES (ID, USER_ID) 
VALUES (default, '233'),(default, '233');

In this context default is not a string. (No quotation marks.)

Upvotes: 0

LR1234567
LR1234567

Reputation: 1246

My guess is that your sequence counter needs to be reset (perhaps you had manually entered numbers in your table instead of using nextval so counter was not incremented) and its giving out numbers you already have in your table. Trying reseting it with something like

ALTER SEQUENCE my_seq RESTART 100000;

Where 100000 is a number greater than:

SELECT MAX(id) FROM entries;

Upvotes: 1

Related Questions