Drizzle TeamDT
Drizzle Team•2y ago
pastilhas

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'),
});


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 });


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)


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
Was this page helpful?