Issue description

The indexPredicate gets escaped as if it is only a single column name in InsertQueryBuilder:

But it should be an expression/predicate, the same used for the WHERE clause of an @Index:

So the resulting WHERE clause contains a double quoted expression/predicate which PostgreSQL does not like.

Expected Behavior

Generate a SQL query without double quoting the whole WHERE clause expression/predicate:

INSERT INTO "example_table"("id", "name", "size_bytes", "metadata_creation", "metadata_update", "metadata_deletion")
VALUES (DEFAULT, $1, $2, DEFAULT, DEFAULT, DEFAULT)
ON CONFLICT ( "name" )
WHERE ( "metadata_deletion" IS NULL ) DO
UPDATE
SET "size_bytes" = EXCLUDED."size_bytes"
RETURNING "id", "metadata_creation", "metadata_update", "metadata_deletion"
-- PARAMETERS: ["Example Entity",1024]

Actual Behavior

Generates a SQL query that double quotes the whole WHERE clause expression/predicate and therefore causes an Error.

Resulting Query:

INSERT INTO "example_table"("id", "name", "size_bytes", "metadata_creation", "metadata_update", "metadata_deletion")
VALUES (DEFAULT, $1, $2, DEFAULT, DEFAULT, DEFAULT)
ON CONFLICT ( "name" )
WHERE ( ""metadata_deletion" IS NULL" ) DO
UPDATE
SET "size_bytes" = EXCLUDED."size_bytes"
RETURNING "id", "metadata_creation", "metadata_update", "metadata_deletion"
-- PARAMETERS: ["Example Entity",1024]

Error:

ERROR: zero-length delimited identifier at or near """"

Steps to reproduce

Have an ExampleEntity:

import { Column, CreateDateColumn, DeleteDateColumn, Entity, Index, PrimaryGeneratedColumn, UpdateDateColumn } from "typeorm";

@Entity("example_table")
export class ExampleEntity {
    @PrimaryGeneratedColumn("identity")
    id!: number;  
    @Index({ unique: true })
    @Column()
    name!: string;
    @Column({ name: "size_bytes" })
    sizeBytes!: number;    
    @Index()
    @CreateDateColumn({ name: "metadata_creation", type: "timestamptz" })  
    metadataCreation!: Date;    
    @Index()
    @UpdateDateColumn({ name: "metadata_update", type: "timestamptz" })  
    metadataUpdate!: Date;  
    @Index({ where: '"metadata_deletion" IS NULL' })  
    @DeleteDateColumn({ name: "metadata_deletion", type: "timestamptz" })  
    metadataDeletion!: Date;
}

Try to insert an ExampleEntity with an InsertQueryBuilder:

const batch: QueryDeepPartialEntity<ExampleEntity>[] = [{
    name: "Example Entity",
    sizeBytes: 1024,
}];

const overwrite: string[] = ["size_bytes"];
const conflictTarget: string[] = ["name"];
const insertOrUpdateOptions: InsertOrUpdateOptions = {
    indexPredicate: '"metadata_deletion" IS NULL',
};

entityManager.createQueryBuilder(ExampleEntity, "example_table")
.insert()
.values(batch)  
.orUpdate(overwrite, conflictTarget, insertOrUpdateOptions)  
.execute();

And get the following Error:

QueryFailedError: zero-length delimited identifier at or near """"

My Environment

Additional Context

It seems that the PostgresQueryRunner also does not escape the WHERE clause of an Index when creating it:

So i guess a simple solution could be to remove the this.escape()?

Turning this:

Into this:

conflictTarget += ` WHERE ( ${indexPredicate} )`

But i do not have enough knowledge to confidently say this won't break anything or if only postgres is affected by this, so comments on this would be appreciated.

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.