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?