2 replies

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

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

Drizzle Team•2y ago•

2 replies

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

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

id

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

duplicate key value violates unique constraint when trying to insert

duplicate key value violates unique constraint when trying to insert

Similar Threads

Similar Threads

Similar Threads