Menu
How can I create a primary key on a materialized view in Postgres?
ALTER MATERIALIZED VIEW my_mat_view ADD PRIMARY KEY (id)
returns error:
Error in query: ERROR: "my_mat_view" is not a table
640
WITH PRIMARY KEY Clause Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh. The master table must contain an enabled primary key constraint.
In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement. You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.
Creating a Materialized View When you create a materialized view in PostgreSQL, it uses a regular database table underneath. You can create database indexes on the materialized view directly and improve performance of queries that access the materialized view.
Materialized views cannot have primary keys. You can use a unique index instead.
create unique index on my_mat_view (id)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
