nestjs pgsql typeorm ON CONFLICT DO UPDATE command cannot affect row a second time

Question

i have this entity

@Entity(DB_TABLES.PAYMENTS.NAME)
@Unique(["paidAmount", "paidDate", "centrelinkRef"])
export class PaymentEntity extends CommonEntity {

  @Column({ name: "actual_amount", type: "decimal", precision: 10, scale: 2, nullable: true })
  actualAmount: number;

  @Column({ name: "payment_status", type: "enum", enum: PaymentStatus, default: PaymentStatus.Paid })
  paymentStatus: PaymentStatus;

  @Column({ name: "paid_amount", type: "decimal", precision: 10, scale: 2, nullable: true, default: 0 })
  paidAmount: number;

  @Column({ name: "paid_date", type: "timestamptz", nullable: true })
  paidDate: Date;

  @Column({ name: "centrelink_ref", type: "varchar", nullable: true })
  centrelinkRef: string;

}

and adding payments

 async _addValidPayments(queryRunnerManager: EntityManager, payments: PaymentDto[]): Promise<ApiResponseDto> {
    // Save the updated entities
    const conflictPaths = ["paidAmount", "paidDate", "centrelinkRef"];
    const res = await queryRunnerManager.upsert(PaymentEntity, payments, {
      conflictPaths, skipUpdateIfNoValuesChanged: true, upsertType: "on-conflict-do-update"
    });
    console.log("PaymentService ~ _addValidPayments ~ res:", res);
    return { msg: APP_MESSAGE.CREATED_SUCCESSFULLY };
  }

but this is showing error

[Nest] 4320  - 02/19/2024, 3:04:06 PM   ERROR [ExceptionsHandler] 
ON CONFLICT DO UPDATE command cannot affect row a second time     
QueryFailedError: ON CONFLICT DO UPDATE command cannot affect row 
a second time
    at PostgresQueryRunner.query (E:\PROJECTS\Neha\orange_rentals\backend\src\driver\postgres\PostgresQueryRunner.ts:299:19)        
    at processTicksAndRejections (node:internal/process/task_queues:95:5)

whats the issue?

Answer 1

 const sqlQuery = `
      INSERT INTO payments ("title",.....)
      VALUES 
      ${payments.map((data) => `('${data.title}'`).join(',')}
      ON CONFLICT ("paid_amount", "paid_date", "payment_source", "title")
      DO NOTHING
      RETURNING id`;
res = await queryRunnerManager.query(sqlQuery);

this worked for me