Prisma PostgreSQL queryRaw error code 42P01 table does not exist-CodePudding

I am trying to run a query that searches items in the Item table by how similar their title and description are to a value, the query is the following:

let items = await prisma.$queryRaw`SELECT * FROM item WHERE SIMILARITY(name, ${search}) > 0.4 OR SIMILARITY(description, ${search}) > 0.4;`

However when the code is run I receive the following error:

error - PrismaClientKnownRequestError: 
Invalid `prisma.$queryRaw()` invocation:


Raw query failed. Code: `42P01`. Message: `table "item" does not exist`
  code: 'P2010',
  clientVersion: '4.3.1',
  meta: { code: '42P01', message: 'table "item" does not exist' },
  page: '/api/marketplace/search'
}

I have run also the following query:

let tables = await prisma.$queryRaw`SELECT * FROM pg_catalog.pg_tables;`

Which correctly shows that the Item table exists! Where is the error?

CodePudding user response：

After doing some light research, It looks like you possibly need double-quotes. Try

let items = await prisma.$queryRaw`SELECT * FROM "Item" ... blah blah

I say this because PostgreSQL tables names and columns default to lowercase when not double-quoted. If you haven't built much of your db, it may be worth wild to make all the tables and columns lowercase so that you won't have to keep adding double quotes and escaping characters.

References: