How to Create a View in PostgreSQL

A view stores the query, not the result, so it is always current and costs whatever the underlying query costs on every read. CREATE OR REPLACE can change the SELECT but cannot remove or reorder existing columns or change their types. Those need a DROP VIEW first, which itself fails if other views depend on it.

CREATE OR REPLACE VIEW active_customers AS
SELECT id, email, last_seen_at
FROM customers
WHERE NOT churned;

Materialized view

A materialized view stores rows on disk, so reads are fast and the data is stale until refreshed. A plain REFRESH takes an exclusive lock and blocks readers for the duration; CONCURRENTLY avoids that but requires a unique index on the view and does more work overall. Neither refreshes itself, so schedule it.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT created_at::date AS day, SUM(amount) FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;