In my nest.js application I have to avoid of Benchmark table population in case ValidFrom and ValidUntil periods are overlapped. As for me the best idea is to use PostgreSQL DB functionality. So I applied the next migration script.

    await queryRunner.query(`
        ALTER TABLE "Benchmark"
        ADD CONSTRAINT no_overlap
        EXCLUDE USING GIST (
          tsrange("ValidFrom", "ValidUntil", '[]') WITH &&
        )
      `);

The applied implementation works perfectly and covers many required case, but unfortunately they have not accepted the solution, because in our application migration scripts should be generated by TypeORM.

So my question is

Is it possible to describe mentioned constraint for records avoiding overlapping dates in mentioned earlier columns? How?

In my nest.js application I have to avoid of Benchmark table population in case ValidFrom and ValidUntil periods are overlapped. As for me the best idea is to use PostgreSQL DB functionality. So I applied the next migration script.

    await queryRunner.query(`
        ALTER TABLE "Benchmark"
        ADD CONSTRAINT no_overlap
        EXCLUDE USING GIST (
          tsrange("ValidFrom", "ValidUntil", '[]') WITH &&
        )
      `);

The applied implementation works perfectly and covers many required case, but unfortunately they have not accepted the solution, because in our application migration scripts should be generated by TypeORM.

So my question is

Is it possible to describe mentioned constraint for records avoiding overlapping dates in mentioned earlier columns? How?

• nestjs
• typeorm
• database-migration
• nestjs-typeorm

1 Answer 1

Try this. I hope this might help. For more: Typeorm Exclusion Feature

@Entity()
@Exclusion(`USING gist ("room" WITH =, tsrange("from", "to") WITH &&)`)
export class RoomBooking {

    @Column()
    room: string;

    @Column()
    from: Date;

    @Column()
    to: Date;
}