Search code examples
postgresqlnestjstypeorm

Type mismatch at TypeORM

I want to search a cookieId within an array in Postgres using TypeORM. The answer for this question is given here: How do I query an array in TypeORM

However, when I implemeted the code:

user = await this.usersRepository
      .createQueryBuilder('user')
      .where('user.userCookieIds @> ARRAY[:cookieId]', {cookieId: userCookieId})
      .getOne();

the relevant entity is:

@Column("varchar", { array: true })
  userCookieIds: string[];

I got an error: QueryFailedError: operator does not exist: character varying[] @> text[]

How could I specify the type for cookieId in postgres in this case, so it would also be varchar?

Solution

  • You should be able to cast the value to the correct type:

    user = await this.usersRepository
      .createQueryBuilder('user')
      .where('user.userCookieIds @> ARRAY[:cookieId]::varchar[]', {cookieId: userCookieId})
      .getOne();

    You could also change your column to use a text array insead of a varchar: https://stackoverflow.com/a/4849030/735398