How to Use the CREATE VIEW Command in PostgreSQL

If you’ve ever found yourself copying and pasting the same complicated SQL query over and over again, views are the feature that will save you from that pain. A view lets you save a query under a name and then treat it like a table, which makes your SQL cleaner, your reports more consistent, and your database easier to work with. In this guide, I’ll walk through the CREATE VIEW command in PostgreSQL in detail, covering syntax, practical examples, updatable views, security considerations, and best practices.

What Is a View?

A view is a virtual table based on the result of a SQL query. Unlike a regular table, a view doesn’t store data on its own (with the exception of materialized views, which is a separate topic). Instead, every time you query a view, PostgreSQL executes the underlying SQL and returns fresh results. This means views are always up to date with the current state of the underlying tables, at the cost of the query needing to be re-run each time.

Views are useful for a few reasons: they simplify complex queries, they can restrict access to certain columns or rows for security purposes, and they provide a stable interface even if the underlying table structure changes slightly.

Basic Syntax of CREATE VIEW

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW view_name [(column_name [, ...])]
AS
SELECT ...
[WITH [CASCADED | LOCAL] CHECK OPTION];

Let’s break down the pieces:

• OR REPLACE: updates an existing view’s definition instead of requiring you to drop and recreate it.
• TEMP / TEMPORARY: creates a temporary view that only exists for the duration of your session.
• view_name: the name you’re giving the view.
• column_name: optionally rename the output columns.
• AS SELECT …: the query that defines what the view returns.
• WITH CHECK OPTION: relevant for updatable views, restricting what INSERT/UPDATE statements against the view can do.

A Simple Example

Let’s say you have an employees table and you frequently need to see only active employees along with their department name. Instead of writing the join every time, you can create a view:

CREATE VIEW active_employees AS
SELECT e.id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = true;

Now, querying active employees is as simple as:

SELECT * FROM active_employees;

No more remembering the join condition or the filter every time.

Naming Columns in a View

By default, a view’s columns take their names from the underlying query. But you can rename them explicitly:

CREATE VIEW employee_summary (employee_id, full_name, department) AS
SELECT e.id, e.first_name || ' ' || e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

This is especially useful when your query includes computed expressions (like string concatenation) that wouldn’t otherwise have a clean default column name.

Using CREATE OR REPLACE VIEW

If you need to update a view’s logic without dropping it (and losing any permissions granted on it), use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW active_employees AS
SELECT e.id, e.first_name, e.last_name, d.department_name, e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = true;

Keep in mind an important restriction: you can add new columns at the end, but you cannot remove existing columns or change their data types using CREATE OR REPLACE VIEW. If you try, PostgreSQL will throw an error, and you’ll need to drop and recreate the view instead.

Creating Views with Joins and Aggregations

Views really shine when your underlying query gets complex. Here’s an example combining a join with aggregation:

CREATE VIEW department_headcount AS
SELECT d.department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id AND e.is_active = true
GROUP BY d.department_name;

Now anyone on your team can run SELECT * FROM department_headcount; and get a clean summary without needing to understand the underlying join and aggregation logic.

Updatable Views

One feature that surprises a lot of newcomers is that some views in PostgreSQL are automatically updatable, meaning you can run INSERT, UPDATE, and DELETE directly against them, and PostgreSQL will translate those operations to the underlying table.

For a view to be automatically updatable, it generally must:

• Be based on a single table or view (no joins, unions, or set operations)
• Not include GROUP BY, HAVING, DISTINCT, LIMIT, OFFSET, or window functions
• Not use aggregate functions

For example:

CREATE VIEW active_employees_simple AS
SELECT id, first_name, last_name, is_active
FROM employees
WHERE is_active = true;

You could run:

UPDATE active_employees_simple SET last_name = 'Smith' WHERE id = 5;

And it would update the underlying employees table directly. This is convenient, but it also means you should be thoughtful about which views you expose for write access.

Using WITH CHECK OPTION

When a view is updatable and includes a WHERE clause, you might want to prevent users from inserting or updating rows in a way that would make them disappear from the view. That’s what WITH CHECK OPTION does:

CREATE VIEW active_employees_checked AS
SELECT id, first_name, last_name, is_active
FROM employees
WHERE is_active = true
WITH CHECK OPTION;

With this option in place, if someone tries to insert a row with is_active = false through this view, PostgreSQL will reject it, because that row wouldn’t satisfy the view’s WHERE clause.

There are two variants:

• LOCAL: only checks the conditions defined in this specific view.
• CASCADED: checks conditions in this view and any views it’s built on top of. This is the default if you just write WITH CHECK OPTION without specifying.

Creating Views for Security Purposes

Views are a great tool for controlling access to sensitive data. Instead of granting a role access to an entire table (which might contain sensitive columns like salary or social security numbers), you can create a view that exposes only the safe columns:

CREATE VIEW employee_directory AS
SELECT id, first_name, last_name, department_id, work_email
FROM employees;

Then grant access to the view instead of the base table:

GRANT SELECT ON employee_directory TO general_staff_role;

This way, general_staff_role can see names and emails but never has direct access to sensitive columns like salary, because it was never granted permissions on the employees table itself.

Row-Level Security with Views

You can also use views to implement a form of row-level filtering, especially in PostgreSQL versions or setups where you’re not using native row-level security policies:

CREATE VIEW my_region_orders AS
SELECT * FROM orders WHERE region = current_setting('app.current_region');

Combined with session variables set by your application, this pattern lets different users see only the rows relevant to them, without duplicating the underlying table.

Temporary Views

If you just need a view for the duration of your current session, perhaps for some ad hoc analysis, you can create a temporary view:

CREATE TEMP VIEW session_summary AS
SELECT user_id, COUNT(*) AS action_count
FROM user_actions
WHERE session_id = current_setting('app.session_id')
GROUP BY user_id;

Temporary views are automatically dropped at the end of your session, so they won’t clutter your schema long-term.

Common Use Cases for CREATE VIEW

1. Simplifying complex reporting queries: turning a multi-join, multi-aggregate query into a single named object.
2. Restricting access to sensitive columns: exposing only safe columns to certain roles.
3. Providing a stable API to applications: even if underlying tables change, a well-designed view can shield application code from those changes.
4. Standardizing business logic: if “active customer” has a specific definition involving multiple conditions, encoding that in a view ensures everyone uses the same definition.
5. Simplifying joins for BI tools: many business intelligence tools work better against flat, pre-joined views than against normalized table structures.

Troubleshooting Common Issues

“View Column X Does Not Exist”

This usually happens when the underlying table structure changed after the view was created, but the view wasn’t updated. Use CREATE OR REPLACE VIEW to refresh the definition.

Cannot Drop or Alter a Column Referenced by a View

If you try to alter a table column that a view depends on, PostgreSQL might block it. You’ll need to drop the dependent view first (or recreate it) before making structural changes to the underlying table.

View Performance Is Slow

Remember, a regular view is not pre-computed. Every query against it re-runs the underlying SQL. If a view is queried frequently and involves expensive joins or aggregations, consider whether a materialized view (which does store results) is a better fit.

Errors When Trying to INSERT/UPDATE Through a View

Not all views are automatically updatable. If your view includes joins, aggregates, or DISTINCT, you’ll need to either simplify it, use INSTEAD OF triggers to define custom update logic, or just perform writes directly against the base tables.

Best Practices for Using CREATE VIEW

• Name views clearly: use a naming convention (like a v_ or vw_ prefix, or a directory-based schema) so it’s obvious which objects are views versus base tables.
• Keep view logic focused: avoid cramming too much business logic into a single view. Smaller, composable views are easier to maintain than one giant do-everything view.
• Use views for access control deliberately: when exposing data to less-trusted roles, always route access through a view rather than granting direct table access.
• Document your views: use COMMENT ON VIEW view_name IS '...' to leave a clear description of what the view is for.
• Watch for performance with nested views: views built on top of other views can become slow if the nesting gets too deep, since PostgreSQL has to expand and optimize the whole chain.
• Consider materialized views for expensive queries: if a view’s underlying query is heavy and doesn’t need real-time freshness, a materialized view with a refresh schedule might serve you better.

Views vs. Materialized Views

A question that comes up constantly once people get comfortable with CREATE VIEW is when to reach for a materialized view instead. The key difference is that a regular view re-runs its underlying query every single time it’s accessed, while a materialized view stores the result physically on disk and only updates when you explicitly refresh it.

CREATE MATERIALIZED VIEW monthly_sales_summary_mat AS
SELECT date_trunc('month', order_date) AS month, SUM(total_amount) AS total_sales
FROM orders
GROUP BY 1;

To update the data later, you run:

REFRESH MATERIALIZED VIEW monthly_sales_summary_mat;

Regular views make sense when you want the data to always be current and the underlying query isn’t too expensive. Materialized views make sense when the query is expensive (heavy joins, large aggregations) and you can tolerate the data being slightly stale, refreshed on a schedule rather than in real time.

Combining Views with UNION

Views aren’t limited to a single SELECT with joins and filters. You can build views around set operations too, which is useful for combining data from structurally similar tables:

CREATE VIEW all_transactions AS
SELECT id, amount, 'sale' AS transaction_type, created_at FROM sales
UNION ALL
SELECT id, amount, 'refund' AS transaction_type, created_at FROM refunds;

This gives applications and reporting tools a single, unified place to query both sales and refunds without needing to know about the underlying table split.

Nesting Views

You can build views on top of other views, which is a powerful way to layer complexity gradually:

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE is_active = true;

CREATE VIEW active_employees_in_engineering AS
SELECT * FROM active_employees WHERE department = 'Engineering';

This keeps each view focused and readable, but be aware that deeply nested views (four or five levels deep) can become harder for the query planner to optimize and harder for humans to trace through when debugging unexpected results. If you notice performance issues with heavily nested views, try flattening the logic into a single view, or consider a materialized view at the layer where the heaviest computation happens.

Granting Access Through Views for Security

As mentioned earlier, views are a great security boundary, but it’s worth expanding on exactly how the permission model works here. When a role queries a view, PostgreSQL checks whether that role has permission on the view itself, not necessarily on the underlying tables. This means you can grant a role access to a view without ever granting it access to the base tables:

CREATE VIEW public_employee_directory AS
SELECT id, first_name, last_name, department FROM employees;

REVOKE ALL ON employees FROM general_staff_role;
GRANT SELECT ON public_employee_directory TO general_staff_role;

This works because of a PostgreSQL mechanism where the view runs with the privileges of its owner by default (similar in spirit to SECURITY DEFINER for functions), letting the view “see” the underlying table even though the querying role cannot access it directly.

Frequently Asked Questions

Can a view have an index?

No, regular views cannot be indexed directly, since they don’t store data. However, materialized views can be indexed, since they do store their result set physically.

Can I use a view inside another query’s FROM clause like a subquery?

Yes, a view behaves just like a table anywhere in a query, including as part of a join, subquery, or CTE.

Does creating a view lock the underlying tables?

CREATE VIEW itself takes a fairly lightweight lock and doesn’t scan or lock the underlying data in a heavy way, since it’s just storing the query definition, not executing it at creation time.

Can views have their own comments and documentation?

Yes, use COMMENT ON VIEW view_name IS 'description here'; to attach documentation directly to the view, which shows up in tools like \d+ view_name in psql.

What happens if the underlying table is dropped?

If you try to drop a table that a view depends on, PostgreSQL will block the operation with a dependency error unless you use CASCADE, in which case the dependent view gets dropped along with the table.

Can I create a view based on a query that includes window functions?

Yes, views can include window functions like ROW_NUMBER(), RANK(), or SUM() OVER (...) without any special handling. Just be aware that such a view won’t be automatically updatable, since window functions fall outside the criteria PostgreSQL requires for automatic updatability.

Is it possible to grant different privileges to different roles on the same view?

Yes, just like with tables, you can run separate GRANT statements for the same view targeting different roles, and each role only gets the specific privileges it was explicitly given. There’s nothing special about how views handle multiple simultaneous grants compared to ordinary tables.

Wrapping Up

CREATE VIEW is one of the most practical tools in PostgreSQL for keeping your SQL clean, your access control tight, and your reporting consistent. Whether you’re simplifying a gnarly join, hiding sensitive columns from certain roles, or giving your BI tool a friendlier interface to query against, views make your database easier to work with. Start simple, use CREATE OR REPLACE VIEW to iterate, and reach for more advanced features like WITH CHECK OPTION and updatable views once you’re comfortable with the basics.