Querying a materialized view: relation does not exist

I define a materialized view as such:

export const usersMv = pgMaterializedView("users_mv").as(
  (qb) => {
    return qb
      .select({
        name: users.name,
      })
      .from(users)
  }
)

export const usersMv = pgMaterializedView("users_mv").as(
  (qb) => {
    return qb
      .select({
        name: users.name,
      })
      .from(users)
  }
)

Then I query this materialized view:

  const results = await db
    .select()
    .from(usersMv)

  const results = await db
    .select()
    .from(usersMv)

But I get the error:

NeonDbError: relation "users_mv" does not exist

NeonDbError: relation "users_mv" does not exist

I'm using Neon. Does Drizzle know how to create the materialized view for me?

3 Replies

joshpachner•7mo ago

hey @pax , I dont have the answer for ya. but you could create the view normally in your database, and then just add the .existing() at the end of your drizzle view declaration. (thats what i do for my views, and so that i have more control over it in writing the raw sql)

samson•5mo ago

been running into the same problem, did you figure this out @pax ?

Justin•5mo ago

Views are not created with code (yet), the pgView and pgMaterializedView are simply ways to reference existing views in your database. It's not a bug, it's just code that has yet to be created within Drizzle

Gaming

Programming

Querying a materialized view: relation does not exist