I am using Postgres 9.4. I have created a materialized view from one of my tables and I want to be able to refresh it concurrently.
I know from the documentation that in order to do a concurrent refresh, I need to have a unique index on the materialized view. But currently I have no unique fields in the view.
This is the current structure of the materialized view:
Materialized view "public.vw_chemical_summary_by_ccg" Column | Type | Modifiers -----------------+----------------------+----------- processing_date | date | pct_id | character varying(3) | chemical_id | character varying(9) | items | bigint | cost | double precision | Indexes: "vw_idx_chem_by_ccg_chem_id" btree (chemical_id) "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)
chemical_id fields should be unique together, but I haven't enforced that anywhere.
So my question is, how can I sensibly create a unique index so that I can do concurrent refreshes?
I guess I could add a new integer column and make that unique, then index it - would that be the most sensible approach?