Materialised view migration erroring, using knex

Question

I have an SQL query that my company mentor wrote that creates a table with columns voucher_Id, quantity, used and remaining, however, he was showing me an example of what I need in my postgres database and wrote it inside of the query tool. I have made some small amendments however when I try to create the Materialized View via a migration the following query throws an error:

Here is his query he wrote in the query tool on postgres which works like a charm:

SELECT v.voucher_id,v.quantity, COUNT(ov.voucher_id) AS "used", v.quantity - COUNT(ov.voucher_id) AS "remaining"
      FROM public.vouchers v
      LEFT OUTER JOIN public.order_vouchers ov ON (v.voucher_id = ov.voucher_id)
      GROUP BY v.voucher_id, v.quantity

And then in my migration using knex I have:

CREATE MATERIALIZED VIEW mv_vouchers
    SELECT * FROM vouchers,
      SELECT v.voucher_id,v.quantity, COUNT(ov.voucher_id) AS "used", v.quantity - COUNT(ov.voucher_id) AS "left"
      FROM public.vouchers v
      LEFT OUTER JOIN public.order_vouchers ov ON (v.voucher_id = ov.voucher_id)
      GROUP BY v.voucher_id, v.quantity

The error is thrown specifically near SELECT, the tables I am using are vouchers and order_id .I am very new to this, any help is greatly appreciated.

Answer 1

You have two SELECT clauses. Is this what you want?

CREATE MATERIALIZED VIEW mv_vouchers as
      SELECT v.voucher_id, v.quantity, COUNT(ov.voucher_id) AS "used", v.quantity - COUNT(ov.voucher_id) AS "left"
      FROM public.vouchers v LEFT OUTER JOIN
           public.order_vouchers ov
           ON v.voucher_id = ov.voucher_id
      GROUP BY v.voucher_id, v.quantity;