duplicate key value violates unique constraint when trying to insert

I have this simple table:

export const ExperimentData = pgTable('experiment_data', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  timestamp: bigint('timestamp', { mode: 'number' }),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  event: varchar('event', { mode: 'string', length: 255 }),
  data: json('data'),
});

export const ExperimentData = pgTable('experiment_data', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  timestamp: bigint('timestamp', { mode: 'number' }),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  event: varchar('event', { mode: 'string', length: 255 }),
  data: json('data'),
});

and i have a script that reads data from redis and writes to this table:

await statisticsdb.insert(ExperimentData).values({ timestamp, websiteId, experimentId, variantId, event, data });

await statisticsdb.insert(ExperimentData).values({ timestamp, websiteId, experimentId, variantId, event, data });

this script runs is supposed to run every hour, triggered by a cronjob and now it doesn't run because i get the error

PostgresError: duplicate key value violates unique constraint "experiment_data_pkey"
 code: "23505"

      at ErrorResponse (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:792:23)
      at handle (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:811:5)
      at data (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:562:3)
      at emit (native:1:1)
      at addChunk (native:1:1)
      at readableAddChunk (native:1:1)
      at data (native:1:1)

PostgresError: duplicate key value violates unique constraint "experiment_data_pkey"
 code: "23505"

      at ErrorResponse (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:792:23)
      at handle (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:811:5)
      at data (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:562:3)
      at emit (native:1:1)
      at addChunk (native:1:1)
      at readableAddChunk (native:1:1)
      at data (native:1:1)

this error is caused on the first insertion try. - I haven't done anything, haven't changed the schema or the data in the database - as you can see, the only primary key i have in the table is the id field - am not trying to force the id I don't know exactly what to do here, this is kind of urgent as our company's analytics depends on this. Also, don't want to mess up the data in Postgres nor in Redis. Any help would be much appreciated. Thank you

Solution:

asked and answered in stack overflow: https://stackoverflow.com/questions/78633513/duplicate-key-value-violates-unique-constraint-when-trying-to-insert?noredirect=1#comment138634231_78633513...

Stack Overflow

duplicate key value violates unique constraint when trying to insert

I'm using drizzle-orm to work on my postgresql database... I have this simple table: export const ExperimentData = pgTable('experiment_data', { id: bigserial('id', { mode: 'number' }).primaryKey(...

Jump to solution

1 Reply

Solution

rubberduckies•7mo ago

asked and answered in stack overflow: https://stackoverflow.com/questions/78633513/duplicate-key-value-violates-unique-constraint-when-trying-to-insert?noredirect=1#comment138634231_78633513

Stack Overflow

duplicate key value violates unique constraint when trying to insert

I'm using drizzle-orm to work on my postgresql database... I have this simple table: export const ExperimentData = pgTable('experiment_data', { id: bigserial('id', { mode: 'number' }).primaryKey(...

Gaming

Programming

duplicate key value violates unique constraint when trying to insert