typeorm: .save() is throwing duplicate key value violates unique constraint

I can confirm that this problem still exists. I’m using typeorm with postgres.

I can confirm that this problem still exists. I’m using typeorm with postgres.

Same here

Having spent the last week building a platform on top of typeorm and then to discover there is no way to save an entity due to this bug is an extremely frustrating situation. I’ve tried working around this in various ways, without success.

Does anybody know of ANY solution to get Typeorm working with Postgres (using dual-foreign keys, not the single primary key uuid pattern)?

I just got this problem, but I am not sure why it only happens if the entity has relationships in my case. I believe you are using migrations with the QueryRunner or restoring a SQL dump.

In the meanwhile, try this after inserting the data in your database (in the end of your migration/seed, for example) (PostgreSQL only):

SELECT setval(
    pg_get_serial_sequence('"your_table_name"', 'your_primary_key_name'),
    (SELECT MAX("id") FROM "your_table_name") + 1
)

Source: https://stackoverflow.com/a/21639138/2957291

It should solve the problem for now.

If I find more information before somebody else here I will paste updates.

@ubershmekel @dulkith @n8sabes @bohendo @oscar-corredor @dekdekbaloo @vogler @aosifre @vamseekm @DrakkenSaer, can you confirm if this solves your problem?

@imnotjames, thank you for your fix https://github.com/typeorm/typeorm/pull/6417.

Just in case you missed it (because this topic is now set at “closed”), this is still not working appropriately in Postgres.

This issue occurs when you are trying to save() an object with its relations.

Example with 2 tables: Photo and Category

photo = { id: 1, category: { id: 1, name: "holidays" } }

// first save works
this.save(photo) // works

// second save does not work
this.save(photo)

It does not work the second time because save() tries to recreate the relation between Photo(1) <=> Category(1).

You should do:

photo = { id: 1, category: { id: 1, name: "holidays" } }

// first save works
this.save(photo)

delete photo.category

// second save works!
this.save(photo)

Had a similar issue

Resolved it by converting all UUID’s on save to lowercase.

An external source was giving me UUID’s in uppercase. Saving them to Postgres worked the first time since Postgres simply converts the UUID to lowercase. On subsequent saves, TypeORM/Postgres is unable to match the lowercase and uppercase UUID’s and tries to save as a new row, only to conflict with the existing row and throw the PK exception.

Any solution on this one? I have 1 PK and 1 UNIQUE and since I’m not passing the PK it will always attempt to insert, which is not desirable since it will raise duplicate key value violates unique constraint, are there any workarounds working?

@saulotoledo Nope, it doesn’t.

Still having troubles with this. Any updates on this? This seems like an important bug to fix.

TypeORM : 0.2.22

@Entity()
export class Device {
	@PrimaryColumn()
	public id: string;

	@Field()
	public name: string;


	@Field(() => [DeviceSetting], { nullable: true })
	@OneToMany(() => DeviceSetting, ds => ds.device, {
		eager: true,
		cascade: ['insert'],
	})
	public settings: DeviceSetting[];
}

@Entity()
@Index(['setting_id', 'device_id'], { unique: true })
export class DeviceSetting extends BaseEntity {
	@Field({
		name: 'id',
	})
	@PrimaryColumn()
	public setting_id: string;

	@PrimaryColumn()
	public device_id: string;

	@ManyToOne(() => Device, d => d.id)
	@JoinColumn({
		name: 'device_id',
	})
	public device!: Device;

	@ManyToOne(() => Setting, s => s.id, {
		eager: true,
	})
	@JoinColumn({
		name: 'setting_id',
		referencedColumnName: 'id',
	})
	public setting: Setting;
}

@Entity()
export class Setting {
	@PrimaryColumn({
		type: 'varchar',
		length: 14,
		unique: true,
		comment: 'The id code of a unique setting entry',
		select: false, // Marked as false because get device setting will generate ambiguous syntax with this selectable
	})
	public id: string;

	@Column()
	public description: string;
}

Indeed an issue, calling .Save on the Device entity will attempt to Insert DeviceSetting relation even if it was previously set in the model, rather than do nothing, it well attempt to insert the previous relation generating duplicate key value violates unique constraint as UQ device_setting.device.id & sevice_setting.setting.id already exists.

I’m still facing the same issue in Postgres. Current solution was to instead of using

@PrimaryGeneratedColumn() id: number;

I now use

@PrimaryGeneratedColumn('uuid') id: number;

It is not a fix but the odds of not having a matching Id helps

This happens for MongoDB nested objects too. Is there any workaround?

Same here, issue still persists when saving nested object with multiple keys in unique constraint - I use POSTGRES

I just got this problem, but I am not sure why it only happens if the entity has relationships in my case. I believe you are using migrations with the QueryRunner or restoring a SQL dump.

In the meanwhile, try this after inserting the data in your database (in the end of your migration/seed, for example) (PostgreSQL only):

SELECT setval(
    pg_get_serial_sequence('"your_table_name"', 'your_primary_key_name'),
    (SELECT MAX("id") FROM "your_table_name") + 1
)

Source: https://stackoverflow.com/a/21639138/2957291

It should solve the problem for now.

If I find more information before somebody else here I will paste updates.

@ubershmekel @dulkith @n8sabes @bohendo @oscar-corredor @dekdekbaloo @vogler @aosifre @vamseekm @DrakkenSaer, can you confirm if this solves your problem?

This worked for me. Thanks.

Same problem with oracle.

Using following entity and getting Error: ORA-00001: Unique Constraint, 'cause TypeORM wants to insert instead of update or in my case do nothing…

export class DayData {

  @Column({name: 'DAY', type: 'date', primary: true})
  day: Date;

  @Column({name: 'ID1', type: 'number', primary: true})
  id1: number;

  @Column({name: 'ID2', type: 'number', primary: true})
  id2: number;

  @Column({name: 'QUANTITY'})
  quantity: number;
}

The problem is, that TypeORM is using a wrong date format during the select:

SELECT 
    "DayData"."DAY" AS "DayData_DAY", 
    "DayData"."QUANTITY" AS "DayData_QUANTITY", 
    "DayData"."ID1" AS "DayData_ID1", 
    "DayData"."ID2" AS "DayData_ID2" 
FROM "DAY_DATA" "DayData" 
WHERE "DayData"."DAY" = :id_0_0 
    AND "DayData"."ID1" = :id_0_1 
    AND "DayData"."ID2" = :id_0_2 
;
-- PARAMETERS: ["2017-12-31T23:00:00.000Z",17,100]

And for the insert this one:

INSERT INTO "DAY_DATA"
    ("DAY", "QUANTITY", "ID1", "ID2") 
VALUES 
    (TO_DATE('2018-01-01', 'YYYY-MM-DD'), :i0_QUANTITY, :i0_ID1, :i0_ID2)
;
-- PARAMETERS: [18025,17,100]

It’s a bit strange, that TypeORM itself casting the date param, but i think it’s ok, because it uses the to_date function. But don’t know if it’s possible to get attacked by sql injection, there - need to test.

Simple entity that this causing this problem with typeorm@0.2.19 and postgres:

import { Column, Entity, PrimaryColumn } from 'typeorm';

@Entity('channel_records')
export class ChannelRecord {
  @PrimaryColumn()
  path!: string;

  @Column({ type: 'json' })
  value!: object;
}

This was working for a while but suddenly started throwing QueryFailedError: duplicate key value violates unique constraint after I restored a row from an external source. The path needs to be case sensitive so can’t just blindly cast them to lower case.

Also here, when saving twice an entity that have OneToOne reslation it throws duplicate key value violates unique constraint.

This issue still persists. When I manually altered the row of records by exporting the whole table and import a new set of data into the table, this error occurs. It seems to me that TypeORM does not recognize the last inserted ID of the table? I have to run the script by @goktugyil in order to solve the issue.

Using the upsert technique I mentioned above, I wanted to know whether a conflict occurred or not.

I had to replace this:

  return (await qb.returning('*').execute()).generatedMaps[0] as T;

With this:

  const executed = await qb.returning('*').execute();
  const wasCreated = !executed.raw[0].data;
  return executed.generatedMaps[0] as T;

But this will probably only correctly differentiate when the entity model does not have a data column. I also wonder if this would be considered a typeorm implementation detail or not.

@dulkith I think the only way to solve it is to manually craft a raw query or use a query builder. You might be able to adapt the snippet I adapted.