CREATE VIEW syntax in PostgreSQL - Deep Dive

Overview - CREATE VIEW syntax

What is it?

A VIEW in a database is like a saved query that you can treat like a table. The CREATE VIEW syntax lets you define this saved query with a name. When you ask for data from the view, the database runs the saved query and shows you the results. This helps you reuse complex queries easily without rewriting them every time.

Why it matters

Without views, you would have to write complex queries repeatedly, which wastes time and can cause mistakes. Views simplify data access by hiding complexity and providing a consistent way to see data. They also help protect sensitive data by showing only what you want users to see. Without views, managing and securing data would be harder and slower.

Where it fits

Before learning CREATE VIEW, you should understand basic SQL SELECT queries and how tables work. After mastering views, you can learn about materialized views for faster performance and about permissions to control who can see or change views.

Mental Model

Core Idea

A view is a named saved query that acts like a virtual table, showing data dynamically when accessed.

Think of it like...

Imagine a restaurant menu that lists dishes without cooking them. The menu is like a view: it shows you what you can order (data) without storing the food itself (table data). When you order, the kitchen prepares the dish fresh, just like the database runs the query when you ask the view.

┌─────────────┐
│   Table A   │
└─────────────┘
      ▲
      │
┌─────────────┐
│   Table B   │
└─────────────┘
      ▲
      │
┌─────────────────────────┐
│       VIEW (saved query)│
│ SELECT columns FROM A    │
│ JOIN B ON condition      │
└─────────────────────────┘
      ▲
      │
┌─────────────┐
│ User Query  │
│ SELECT *    │
│ FROM VIEW   │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic SELECT Queries

Concept: Learn how to write simple SELECT statements to get data from tables.

A SELECT query asks the database to show certain columns and rows from a table. For example, SELECT name, age FROM users; shows the name and age columns from the users table.

Result

You get a list of names and ages from the users table.

Knowing how SELECT works is essential because views are built from SELECT queries.

2

Foundation

What is a View in SQL?

Concept: Introduce the idea of a view as a saved SELECT query that behaves like a table.

A view stores a SELECT query with a name. When you query the view, the database runs the stored SELECT and shows the results. Views do not store data themselves; they show data from underlying tables.

Result

You can use the view name in queries just like a table name.

Understanding that views are virtual tables helps you see how they simplify complex queries.

3

Intermediate

CREATE VIEW Syntax Basics

Concept: Learn the exact syntax to create a view in PostgreSQL.

The basic syntax is: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; This creates a view named view_name that runs the SELECT query when accessed.

Result

A new view is created and can be queried like a table.

Knowing the syntax lets you save queries for reuse and clarity.

4

Intermediate

Using Views to Simplify Complex Queries

🤔Before reading on: do you think views store data or just the query? Commit to your answer.

Concept: Views can hide complex joins and filters behind a simple name.

Instead of writing a long join every time, create a view with that join. Then query the view simply with SELECT * FROM view_name; This saves time and reduces errors.

Result

Queries become shorter and easier to read.

Understanding that views store queries, not data, helps you use them to simplify your work.

5

Intermediate

Updating Data Through Views

🤔Before reading on: do you think you can always update data through a view? Commit to yes or no.

Concept: Some views allow updating underlying tables, but not all.

If a view is simple (selects from one table without joins or aggregates), you can update data through it. Complex views usually cannot be updated directly.

Result

You can sometimes use views to change data, but you must know the rules.

Knowing when views support updates prevents confusion and errors.

6

Advanced

Managing View Dependencies and Refreshing

🤔Before reading on: do you think views store data that needs refreshing? Commit to yes or no.

Concept: Views do not store data, so they always show current data from tables. But materialized views do store data and need refreshing.

Regular views run the query fresh each time. If underlying tables change, views show updated data automatically. Materialized views store results and must be refreshed manually or on schedule.

Result

Views always show current data without extra work.

Understanding this difference helps you choose between views and materialized views.

7

Expert

Security and Performance Considerations with Views

🤔Before reading on: do you think views improve security by themselves? Commit to yes or no.

Concept: Views can restrict data visibility but do not inherently improve performance.

Views can hide sensitive columns or rows by selecting only allowed data. However, views run the underlying query each time, so complex views can slow queries. Indexes on base tables help performance, but views themselves cannot be indexed unless materialized.

Result

Views help control data access but require careful design for performance.

Knowing the limits of views in security and speed guides better database design.

Under the Hood

When you query a view, PostgreSQL replaces the view name with the saved SELECT query internally. It then runs this query against the current data in the base tables. The view itself does not store any data or results. This means views are always up-to-date but can be slower if the query is complex.

Why designed this way?

Views were designed to provide a reusable, named query without duplicating data. This saves storage space and ensures data consistency. Storing data separately would require synchronization and risk stale data. The tradeoff is that views can be slower than tables because the query runs every time.

┌───────────────┐
│ User Query    │
│ SELECT * FROM │
│ view_name     │
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ View Definition (SELECT ...) │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────┐   ┌─────────────┐
│ Base Table A│   │ Base Table B│
└─────────────┘   └─────────────┘
              ▲
              │
       Data read live from tables

Myth Busters - 4 Common Misconceptions

Quick: Does a view store data separately from tables? Commit to yes or no.

Common Belief: Views store their own copy of data like tables do.

Reality: Views do not store data; they run the saved query on base tables each time you access them.

Why it matters: Thinking views store data can lead to confusion about data freshness and storage needs.

Quick: Can you always update data through any view? Commit to yes or no.

Common Belief: You can update data through any view just like a table.

Reality: Only simple views without joins or aggregates support updates; complex views do not.

Why it matters: Trying to update complex views causes errors and wasted effort.

Quick: Do views improve query performance by default? Commit to yes or no.

Common Belief: Using views always makes queries faster.

Reality: Views run the underlying query each time, so they can be slower if the query is complex.

Why it matters: Assuming views speed up queries can lead to poor performance if not designed carefully.

Quick: Does creating a view automatically restrict user access to data? Commit to yes or no.

Common Belief: Views automatically protect sensitive data from unauthorized users.

Reality: Views can help restrict data, but you must set proper permissions; views alone do not secure data.

Why it matters: Relying on views without permissions risks exposing sensitive information.

Expert Zone

1

Views can be nested, meaning a view can be defined using other views, which can complicate query planning and performance.

2

PostgreSQL allows creating views with the WITH CHECK OPTION to ensure data modifications through the view meet the view's conditions.

3

Using SECURITY DEFINER on views can run the view with the creator's permissions, allowing controlled data access.

When NOT to use

Avoid using views when you need fast repeated access to large complex query results; use materialized views instead. Also, do not rely on views alone for security; use proper role-based permissions. For write-heavy operations, avoid updating through views unless they are simple and support it.

Production Patterns

In production, views are used to simplify reporting queries, enforce data abstraction layers, and provide consistent APIs for applications. Materialized views are used for caching expensive query results. Views combined with role-based access control help enforce data security policies.

Connections

Materialized Views

Builds-on

Understanding regular views helps grasp materialized views, which store query results for faster access but require manual refreshing.

Database Permissions

Complementary

Knowing how views work aids in designing permission schemes that restrict data access effectively using views and roles.

Functional Programming

Similar pattern

Views are like pure functions: they produce results based on inputs (tables) without side effects or storing state, helping understand stateless computation.

Common Pitfalls

#1 Trying to update data through a complex view with joins.

Wrong approach:

UPDATE complex_view SET column = 'value' WHERE id = 1;

Correct approach:

UPDATE base_table SET column = 'value' WHERE id = 1;

Root cause:

Misunderstanding that only simple views support updates; complex views are read-only.

#2 Assuming views improve query speed automatically.

Wrong approach:

CREATE VIEW slow_view AS SELECT * FROM large_table JOIN other_table ON ...; -- then expecting faster queries

Correct approach:

Use materialized views or optimize base tables and indexes for performance.

Root cause:

Confusing view abstraction with performance optimization.

#3 Not setting permissions on views, exposing sensitive data.

Wrong approach:

CREATE VIEW sensitive_view AS SELECT * FROM confidential_table; -- no permissions set

Correct approach:

GRANT SELECT ON sensitive_view TO authorized_users; REVOKE ALL ON confidential_table FROM public;

Root cause:

Assuming views alone secure data without proper access control.

Key Takeaways

CREATE VIEW defines a named saved query that acts like a virtual table without storing data.

Views simplify complex queries and provide a consistent way to access data but do not improve performance by themselves.

Only simple views support updating data through them; complex views are read-only.

Views always show current data from base tables because they run the query fresh each time.

Proper permissions must be set on views to protect sensitive data; views alone do not secure data.