SQL CREATE OR REPLACE VIEW

What is SQL CREATE OR REPLACE VIEW?

Creates a new view or updates an existing one atomically without dropping dependent objects.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE OR REPLACE VIEW: PostgreSQL, MySQL (8.0+), MariaDB, SQL Server (from 2016 via CREATE OR ALTER VIEW), Oracle, Snowflake, Redshift, BigQuery (CREATE OR REPLACE VIEW syntax)

SQL CREATE OR REPLACE VIEW Full Explanation

CREATE OR REPLACE VIEW is a Data Definition Language (DDL) command that either generates a new logical view or rewrites the definition of an existing view in a single, transactionally safe step. If the view already exists, the database engine replaces its stored SELECT statement while preserving permissions and dependent objects (such as grants or other views) when possible. This avoids the need to issue a separate DROP VIEW followed by CREATE VIEW, reducing downtime and dependency breakage. The new definition must be compatible with any objects that depend on the view, otherwise the statement fails. Some dialects allow the optional RECURSIVE keyword or WITH CHECK OPTION constraints, but availability varies.

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name
[ ( column1, column2, ... ) ]
AS
SELECT column_expr [, ...]
FROM table_or_view
[ WHERE condition ]
[ WITH [ CASCADE | LOCAL ] CHECK OPTION ];

SQL CREATE OR REPLACE VIEW Parameters

view_name (identifier) - Name of the view to create or replace
column_list (identifiers) - Optional explicit column names if you want to rename or reorder columns in the view
SELECT statement (query) - The query that defines the view's virtual table
WITH CHECK OPTION (keyword) - Enforces that inserts/updates via the view satisfy the view predicate (dialect specific)
CASCADE (keyword) - Propagates CHECK OPTION to underlying views (some dialects)

Example Queries Using SQL CREATE OR REPLACE VIEW

--Create a new or updated view of active customers
CREATE OR REPLACE VIEW active_customers AS
SELECT id, email, created_at
FROM customers
WHERE status = 'active';

--Add explicit column list and a check option
CREATE OR REPLACE VIEW recent_orders (order_id, placed_at, amount)
AS
SELECT id, created_at, total
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
WITH LOCAL CHECK OPTION;

Expected Output Using SQL CREATE OR REPLACE VIEW

If the view did not exist, it is created
If it existed, its definition is updated in place
No rows are returned; a confirmation message such as "CREATE VIEW" or "CREATE OR REPLACE VIEW" is generated

Use Cases with SQL CREATE OR REPLACE VIEW

Modify a view in production without dropping and recreating it
Preserve permissions on an existing view while updating its logic
Deploy view changes via migration scripts with zero downtime
Quickly iterate on analytic views during development

Common Mistakes with SQL CREATE OR REPLACE VIEW

Changing the column list in a way that breaks dependent views or functions
Assuming CREATE OR REPLACE will drop grants; it usually preserves them
Forgetting to include ALL required columns when omitting the explicit column list
Relying on features (e.g., WITH CHECK OPTION) not supported in your dialect

First Introduced In

PostgreSQL 7.3 (2002)

Frequently Asked Questions

Does CREATE OR REPLACE VIEW lock the underlying tables?

No. It only rewrites the view metadata and does not lock source tables for reads or writes beyond the brief metadata update.

Can I add new columns while replacing a view?

Yes, as long as dependent objects do not rely on the old column list. If they do, the statement fails until those dependencies are updated.

How do I rename a view using CREATE OR REPLACE?

You cannot. Use ALTER VIEW RENAME TO or DROP VIEW followed by CREATE VIEW with the new name.

Is WITH CHECK OPTION mandatory?

No. INCLUDE it only when you want to enforce that DML through the view satisfies the view's WHERE clause.