Prisma PostgreSQL queryRaw error code 42P01 table does not exist

Question

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?

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
– drethedevjs, Commented Sep 12, 2022 at 17:37
@drethedevjs I have tried both with Item and with item, still does not work... — DRE
– DRE, Commented Sep 12, 2022 at 17:51
I tried Item because in the database the table name is Item — DRE
– DRE, Commented Sep 12, 2022 at 17:53
Is this your first db query in your app? If not, are all the other queries of specific tables working? — drethedevjs
– drethedevjs, Commented Sep 12, 2022 at 18:21

drethedevjs · Accepted Answer · 2022-09-12 18:38:34Z

6

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:

answered Sep 12, 2022 at 18:38

drethedevjs

5042 gold badges5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Prisma PostgreSQL queryRaw error code 42P01 table does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related