Partial constraints not working

[netogerbi]

Describe the bug

Partial constraints not working. It's creating a primary key constraint instead of a partial unique.

To Reproduce

I have an entity that uses soft deletion. So I need a partial constraint that uses the deleted field of the entity.

I've tried many ways to do it but any of them are working:

an example of the entity:

@Entity('customer_products')
export class CustomerProduct extends BaseEntity {
    @Expose()
    @Transform(({ value }) => value || uuidv4())
    @PrimaryColumn({ type: 'uuid' })
    @IsUUID()
    public id: string;

    @Column({ type: 'text' })
    @Expose()
    @Transform(({ value }) => value || uuidv4())
    @IsUUID()
    public customerId: string;

    @Column({ type: 'uuid' })
    @IsUUID()
    public productId: string;

    @Column()
    @Expose()
    @Transform(({ value }) => new Date(value || Date.now()))
    @CreateDateColumn({
        precision: 3,
        type: 'timestamp',
    })
    @IsDate()
    public created: Date;

    @UpdateDateColumn({
        nullable: true,
        precision: 3,
        type: 'timestamp',
    })
    @Type(() => Date)
    @IsOptional()
    @IsDate()
    public updated?: Date | null;

    @DeleteDateColumn({
        nullable: true,
        precision: 3,
        type: 'timestamp',
    })
    @Type(() => Date)
    @IsOptional()
    @IsDate()
    public deleted?: Date | null;
}

1. create the constraint using a query:

With this approach, the pg-mem converts the constraint into a pkey constraint as follows:

db.executeRaw(`CREATE UNIQUE INDEX IF NOT EXISTS "customer_products_customerId_productId_deleted_idx"
ON customer_products ("customerId", "productId") WHERE "deleted" IS NULL;`);

the executeRaw method above is just wrapping the newDb.public.none.

2. Using the decorators in the entity:

I've add the following decorator:

@Index(['customerId', 'productId'], {
    unique: true,
    where: 'deleted IS NULL',
})
@Entity('customer_products')
export class CustomerProduct extends BaseEntity {
// code

both results in the same issue:

the output of the pg-mem in the tests:

[QueryFailedError: ERROR: insert into "customer_products" (id, customerId, productId, created, updated, deleted) values ($1, $2, $3, $4, $5, $6) returning "customerId,productId" - duplicate key value violates unique constraint "customer_products_pkey"
    DETAIL: Key (customerId,productId)=(e9c88024-90e3-487d-af44-62c5f4e5b689,51814d39-3262-48bf-a497-74960a2d58d5) already exists.·
    🐜 This seems to be an execution error, which means that your request syntax seems okay,
        but the resulting statement cannot be executed → Probably not a pg-mem error.·
    *️⃣ Failed SQL statement: INSERT INTO "customer_products"("id", "customerId","productId", "created", "updated", "deleted") VALUES ( "I REMOVED THESE DATA" ) RETURNING "created", "updated", "deleted";·
    👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:·
    ]

so pg-mem have created the unique index with a different name and as a primary_key as it says duplicate key value violates unique constraint "customer_products_pkey"

the tests:

the db it's being setup using the following:

                 TypeOrmModule.forRootAsync({
            dataSourceFactory: async () =>
                this.db.adapters.createTypeormDataSource({
                        entities: ['src/**/*.entity.ts'],
                    ],
                    subscribers: ['src/**/*.subscriber.ts'],
                    synchronize: true,
                    type: 'postgres',
                }),
            useFactory: () => ({}),
        });

and the tests:

        it('should not allow duplicates even with different IDs', async () => {
            const cp1 = await CustomerProduct.create({
                customerId: customer.id,
                productId: product.id,
            }).save();

            await expect(
                CustomerProduct.create({
                    // customer id is diff - should save
                    customerId: (customer = await Customer.create({
                        name: 'test',
                    }).save()).id,
                    productId: product.id,
                }).save(),
            ).resolves.toBeDefined();

            const cp2Promise = CustomerProduct.create({
                customerId: customer.id, // constraint same as cp1
                productId: product.id, // constraint same as cp1
            });

            // should throw with the same constraints and cp1 is not deleted
            await expect(cp2Promise.save()).rejects.toThrow();

            await cp1.softRemove();

            // should save once cp1 is deleted
            await expect(cp2Promise.save()).resolves.toBeInstanceOf(CustomerProduct);
        });

pg-mem version

"name": "pg-mem", "version": "2.6.13",

[cuongrep]

Hi @netogerbi, Did you solve the issue?