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

[W0lfbane]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

This was working for me earlier but now it is not. I am using a composite key of three different properties. Every time I try to update and existing row, I get the error QueryFailedError: duplicate key value violates unique constraint "PK_8b1375852f33d30b328388e5a5c".

This is my entity:

'use strict';

import * as _ from 'lodash';
import { Entity, Column, PrimaryColumn, Generated } from "typeorm";


@Entity()
export class Assortment {

	@Column("uuid")
	@Generated("uuid")
	id!: string;

	@PrimaryColumn()
	accountId!: string;

	@PrimaryColumn()
	entityType!: string;

	@PrimaryColumn()
	parentId!: string;

	@Column({
		nullable: true
	})
	parentType!: string;

	@Column('varchar', {
		nullable: true
	})
	sortedIds!: string[];

	@Column('jsonb', {
		nullable: true
	})
	children!: Assortment[];
};

[aosifre]

I have exactly the same problem with sqlite.

[vamseekm]

Same kind of issue on postgres for me. I have a composite unique key (foreign key + a date column) throw error "QueryFailedError: duplicate key value violates unique constraint " when ever I use save method on this particular table.

[aosifre]

Still no patch concerning this duplication problem when backing up data?

[vogler]

I ran into the same issue in #3238. Using find + insert/update instead of just save is ugly.

[vogler]

As a workaround I added a PrimaryColumn id that is the concatenation of all the originally as primary keys intended fields. Also ugly, but can be easily removed once this is fixed.

[ubershmekel]

find + insert/update was not working for me. Somehow the find was missing the instance. I don't know how. I tried out this upsert implementation from https://github.com/danielmhanover/typeorm-upsert

Which was great aside from:

• A small bug - it didn't use " quotes for the column names.
• I needed to provide the query builder from the connection, because I have multiple connections. This avoided the Connection "default" was not found error.
• I removed the lodash just to keep things simple.

Here's my final workaround.

import { ObjectType, SelectQueryBuilder } from "typeorm";

/*
* entityType - TypeORM Entity
* obj - Object to upsert
* key_naming_transform (optional) - Transformation to apply to key names before upsert
* do_not_upsert - Keys to exclude from upsert. This is useful if a non-nullable field is required in case
* the row does not already exist but you do not want to overwrite this field if it already exists
*/
export async function upsert<T>(
  entityType: ObjectType<T>,
  obj: T,
  primary_key: string,
  queryBuilder: SelectQueryBuilder<any>,
  opts?: {
    key_naming_transform: (k: string) => string,
    do_not_upsert: string[],
  },
): Promise<T> {
  const keys: string[] = Object.keys(obj);
  for (const unkey of (opts ? opts.do_not_upsert : [])) {
    delete keys[unkey];
  }
  const setter_string =
    keys.map((k) => `"${opts ? opts.key_naming_transform(k) : k}" = :${k}`);

  const qb = queryBuilder
    .insert()
    .into(entityType)
    .values(obj)
    .onConflict(`("${primary_key}") DO UPDATE SET ${setter_string}`);

  keys.forEach((k) => {
    qb.setParameter(k, (obj as any)[k]);
  });

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

[dekdekbaloo]

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

[ThCC]

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

Same here

[oscar-corredor]

same here

[NickKelly1]

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.

[bohendo]

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.

[derSoerrn95]

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.

[n8sabes]

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)?

[dulkith]

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)?

You find any solution for this? I am also stuck same problem.

[ubershmekel]

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.

[lapwat]

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)

[saulotoledo]

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?

[manan]

@saulotoledo Nope, it doesn't.

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

[manan]

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)

@lapwat Are you saying that we cannot use .save to cascade updates down related entities, but we can use it to update just the primary entity (and no related entities)? I agree, .save works fine for individual entities, it just fails when you have related entities.

[josipbagaric]

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.

[huantaoliu]

any updates on this? Still failling for me using postgres with dual keys (2 columns) as primary key.

[Evilart86]

I just removed all objects from table, and then i saved my model without error.
I tried to reproduce but it works good now..

[guillenotfound]

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?

[jhz7]

Hi guys. I was getting the same problem a few hours ago. I have discovered that if you don't tell entity id explicitly, typeorm is gonna try to create a new one. I have just initialized id (when it exists) and it solved problem.

This aproach was generating the issued error:

@Entity('my_entity')
class MyEntity {

  constructor(domain: MyDomainObject) {

    if(domain) {
      this.title = domain.title;
      this.year = domain.year;
    }
  }

  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  title!: string;
  
  @Column()
  year!: number;

}
`
But this one solved (at least for me):

`
 @Entity('my_entity')
 class MyEntity {

  constructor(domain: MyDomainObject) {

    if(domain) {
      this.title = domain.title;
      this.year = domain.year;
    }

    if(domain.id) {
      this.id = domain.id;
    }
  }

  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  title!: string;
  
  @Column()
  year!: number;

}

PD:

• I'm using mysql 5 version.
• title and year togther, are one Unique constraint.

[imnotjames]

@JarLowrey seems unrelated to what's described in this thread.

---

Most of the examples here seem to be with multiple primary columns. Is that the case?

If so, anyone want to confirm that #6417 fixes / doesn't fix the issue?

[DanielRamosAcosta]

Is there any workaround while a patch is being released? @imnotjames

[sshhawwnn]

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.

[cohlar]

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

[DanielRamosAcosta]

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

[DieserMerlin]

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

[DieserMerlin]

This is my workaround. Hope this helps someone.

Basically I just created a repository wrapper and use extends SaveSafeRepository<User> instead of extends Repository<User> to have a user-repository that behaves as expected when I try to save an existing entity.

import {DeepPartial, EntityRepository, Repository, SaveOptions, ObjectID} from 'typeorm';
import {IdEntity} from './id-entity';

@EntityRepository()
export class SaveSafeRepository<T> extends Repository<T> {
  save<T extends DeepPartial<T>>(
    entity: T & {_id?: ObjectID},
    options?: SaveOptions
  ): Promise<T> {
    // @ts-ignore
    if (!entity._id) return this.create(entity);
    // @ts-ignore
    else return this.update(entity._id, entity);
  }
}

Note that you possibly need to change the _id property to the name you use for IDs.

[cooperfrench95]

In case this helps at all. I had a situation where updating a one-to-one foreign key on a table would cause this error. What fixed it for me was simply creating a new entity rather than updating the existing one in order to get a new ID, then saving that new ID as the foreign key on the other table.

[odravison]

I'm facing the same issue here. Any update about fixing this wrong behavior ?

[mreppo]

check for existing record with findOne and save in case records does not exist, also this will prevent increasing autoincrement in case of you use maria|mysql, and InnoDB

[allencoded]

This is still not working appropriately in Postgres.

[twigs67]

Is anyone from TypeOrm looking into this? This seems to be a huge issue.

[Dimitri3502]

I'm using a number as a primary key.
For me this error occurs when I try to save a list which includes duplicated items.
The method save<T extends DeepPartial>(entities: T[], options?: SaveOptions): Promise<(T & Entity)[]> throws the error "duplicate key value violates unique constraint".
The solution for me is to handle duplicates in my list before calling save()

[BaoPham92]

You could potentially just remove the unique options from the Decorators and still yet use the corresponding Column Decorators with your original logic for the time being.

[DedaDev]

Struggled with this for the last 5 hours, finally made it.

So first add a name to constraint in your entity,

@Entity()
@Unique('constraint_name', ['col_one', 'col_two'])

Then, you can use onConflict with ON CONSTRAINT

this.createQueryBuilder()
        .insert()
        .into(YourEntity)
        .values(yourValues)
        .onConflict(`ON CONSTRAINT constraint_name DO UPDATE SET whatever = 1`)
        .execute()
 );

[emagnier]

@imnotjames, thank you for your fix #6417.

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

[jcrben]

@emagnier fyi this is working for me in postgres right now - I initially thought it wasn't but that was due to some bad data in my dev env

[RicardofVallejo]

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

[sjohns21]

This happened to me because I inserted records using literal, numeric values (instead of DEFAULT or undefined) as arguments for the auto-incremented column. Doing so circumvents the column's underlying sequence object's increment call, hence making the sequence's value out of sync with the values in the column in the table.

[Form1ca]

still bug not fixed..

[jcrben]

@Form1ca can you provide a minimal reproduction? ideally in a repo

[oscardianno]

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.

Excuse me, in this case, "your_table_name" is the name of the main table in both cases? The related table isn't meant to be included anywhere in the query?

[rafalebacalla]

I'm currently using Nest and SQLite, and the error is still there.
But I fixed it by manually changing the migration files. I found out that even though the @Column() already had { unique: false }, its still unique in the migrations.

This is the non-unique field

But it generated unique in the migrations. I just removed this part of the code.

[imnotjames]

The original issue has been corrected. If you believe there's still a problem please open a new issue with a way to reproduce the problem.