2

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?

DRE
  • 263
  • 8
  • 35
  • I'm not a PostgreSQL person but does the table name that you're querying have the same casing as what's in the db? For example, does the db say `Item` as opposed to `item`? – drethedevjs Sep 12 '22 at 17:37
  • @drethedevjs I have tried both with `Item` and with `item`, still does not work... – DRE Sep 12 '22 at 17:51
  • I tried `Item` because in the database the table name is `Item` – DRE Sep 12 '22 at 17:53
  • Is this your first db query in your app? If not, are all the other queries of specific tables working? – drethedevjs Sep 12 '22 at 18:21

1 Answers1

4

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:

drethedevjs
  • 404
  • 2
  • 4
  • 15