Drizzle Team•11mo ago

How to make a select from "materialized_view" where clause

Hello guys, maybe you can help me, look that example: I have a simple table with rows and one materialized view like this in PostgresSQL:

CREATE TABLE "tb_test" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "name" varchar(100) NOT NULL
);
INSERT INTO "tb_test" ("name") VALUES ('foo'), ('bar'), ('bin');
CREATE MATERIALIZED VIEW "mv_test" AS SELECT * from "tb_test";

CREATE TABLE "tb_test" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "name" varchar(100) NOT NULL
);
INSERT INTO "tb_test" ("name") VALUES ('foo'), ('bar'), ('bin');
CREATE MATERIALIZED VIEW "mv_test" AS SELECT * from "tb_test";

Using PSQL i can make a select directly into mv_test like a SELECT * FROM "mv_test" WHERE "name" = 'foo'; but i can't do it using drizzle-orm, looks:

import {drizzle} from 'drizzle-orm/postgres-js';
import {eq} from 'drizzle-orm';
import postgres from 'postgres';
import {varchar, pgMaterializedView, uuid} from 'drizzle-orm/pg-core';

const client = postgres('postgresql://postgres:postgres@localhost:5432/postgres');
export const db = drizzle(client);

const mvTest = pgMaterializedView('mv_test', {
  id: uuid('id'),
  name: varchar('name', { length: 100 })
})

db.select().from(mvTest).where(eq(mvTest.name, 'foo')).then(console.log).catch(console.error)

import {drizzle} from 'drizzle-orm/postgres-js';
import {eq} from 'drizzle-orm';
import postgres from 'postgres';
import {varchar, pgMaterializedView, uuid} from 'drizzle-orm/pg-core';

const client = postgres('postgresql://postgres:postgres@localhost:5432/postgres');
export const db = drizzle(client);

const mvTest = pgMaterializedView('mv_test', {
  id: uuid('id'),
  name: varchar('name', { length: 100 })
})

db.select().from(mvTest).where(eq(mvTest.name, 'foo')).then(console.log).catch(console.error)

What do I need to do to pass a clause in a materialized view using drizzle-orm?

2 Replies

NeonCop•2mo ago

running into this

tcintra•2mo ago

Me too

Gaming

Programming

How to make a select from "materialized_view" where clause