CREATE VIEW
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Creates an unmaterialized view.
SingleStore supports both writable and non-unwritable views.INSERT
, UPDATE
, or DELETE
queries on a non-writable view result in an error.ALTER VIEW
command or update data directly in the underlying tables from which the view is created.
Writable views support INSERT
, UPDATE
, and DELETE
queries.
Syntax
CREATE[DEFINER = { <user> | CURRENT_USER }][SCHEMA_BINDING = { ON | OFF }]VIEW [IF NOT EXISTS] <view_name>AS <select_statement>
Writable Views
Inserting, updating, or deleting rows in a writable view also affects the rows in the underlying table.
Enable Writable Views
To enable creation of writable views, set the enable_
global variable to 1
.
SET GLOBAL enable_writable_views = 1;
Not all views are writable.information_
information schema view to inspect if a view can be updated.
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWSWHERE TABLE_NAME='shipped_products';
+------------------+--------------+
| TABLE_NAME | IS_UPDATABLE |
+------------------+--------------+
| shipped_products | YES |
+------------------+--------------+
Whether a view supports INSERT
and DELETE
queries depends on the view definition.
Note
Information schema views are not writable.
Writable View Definition Limitations
To create a writable view that can be updated, the view definition must not contain the following:
-
Aggregate functions or window functions
-
DISTINCT
,UNION
,UNION ALL
,EXCEPT
, orINTERSECT
clause -
GROUP BY
,HAVING
,JOIN
, orLIMIT
clause -
References to non-table entities, such as a view or derived table, in the
FROM
clause -
Complex expression or derived (non-simple) column references in the
SELECT
subquery
To support INSERT
queries, the view definition must conform to the restrictions specified earlier as well as the following:
-
The view definition must not contain multiple references to the same column from the underlying table.
-
The view definition must contain all the columns from the underlying table that do not have a default value.
Permissions Required
The user that defines the view (view definer) must have the INSERT
, UPDATE
, or DELETE
permissions (or privilege) to the underlying table to perform the respective operations on the view.INSERT
, UPDATE
, or DELETE
permissions to grant the respective permission on the view to other users.INSERT
, UPDATE
, or DELETE
) privilege for the view to perform the respective write operations on the view.
The following permissions apply to writable views: INSERT
, UPDATE
, and DELETE
.
GRANT INSERT, UPDATE, DELETE ON <view_name> TO <user>;
If the view definer's INSERT
, UPDATE
, or DELETE
permission to the underlying table is revoked (for example by SUPER
or admin users), then the respective INSERT
, UPDATE
, or DELETE
privilege is revoked for other users as well.
Remarks
The following apply to both writable and non-writable views.
-
Permissions:
-
The user who created the view is granted the ability to
CREATE
,ALTER
, andSELECT
from the view. -
The
SELECT
permission on the view is required to query it. -
The following permissions apply to views:
SELECT
,CREATE VIEW
,ALTER VIEW
,DROP VIEW
,SHOW VIEW
Refer to the permissions matrix for details.
-
-
SingleStore does not support MySQL’s
CREATE OR REPLACE VIEW
syntax.To modify an existing view, use ALTER VIEW
. -
By default, SingleStore restricts the ability to
CREATE
,ALTER
, orSELECT
from the view to the user who created the view and toSUPER
users.In MySQL terms, this is the equivalent of including DEFINER = CURRENT_
andUSER SQL SECURITY DEFINER
in theCREATE VIEW
statement. -
Views can reference views or tables from other databases.
-
CREATE VIEW view_
will replacename AS SELECT * FROM table_ name . . . *
with the column names at the time ofCREATE VIEW
execution rather than during execution ofSELECT .
.. . FROM view_ name It is important to consider this distinction if table_
is altered between the execution ofname CREATE VIEW
andSELECT .
.. . FROM view_ name -
Any function (including
CURRENT_
andUSER() NOW()
) used in a view are evaluated during execution ofSELECT .
, not when the view is created.. . FROM view_ name -
The
DEFINER
clause tells SingleStore which user to use for security checks when a view is referenced by a query.The default value is CURRENT_ USER. -
When
SCHEMA_
is set toBINDING ON
, objects referenced by the view cannot be dropped if the view exists; the view must be dropped before dropping these objects.By default, SCHEMA_
is set toBINDING OFF
. -
The following note applies when the engine variable
table_
is set toname_ case_ sensitivity OFF
: After you create a view, you cannot create another view having the same view name with a different case.Refer to Database Object Case-Sensitivity for more information. -
This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStore Commands ).
-
This command causes implicit commits.
Refer to COMMIT for more information. -
Refer to the Permission Matrix for the required permission.
Examples
The following examples create views that reference a table in the same database as the view.
CREATE VIEW person_view AS SELECT first_name, last_nameFROM table_name WHERE user_id = 'real_person';
CREATE VIEW active_items_view AS SELECT nameFROM items WHERE status = 'active';
CREATE VIEW discounted_items_view AS SELECT nameFROM active_items_view WHERE discount = 1;
The following example creates a view that references two tables (orders
and customers
) that are located in different databases (orders_
and customers_
).
CREATE VIEW customer_orders AS SELECT o.id, c.last_name, c.first_nameFROM orders_db.orders o, customers_db.customers c WHERE o.customer_id = c.id;
Example: Create a Writable View
This example creates a writable view using the following orders
table and then performs insert and delete operations on the view:
CREATE TABLE orders (product_id INT,quantity INT);INSERT INTO orders (product_id, quantity) VALUES(101, 5),(102, 3),(101, 2),(103, 4),(102, 6),(101, 3),(104, 7),(103, 2),(102, 4),(101, NULL);
-
Enable writable views:
SET GLOBAL enable_writable_views = 1; -
Create a view:
CREATE VIEW shipped_products ASSELECT product_id, quantity FROM ordersWHERE quantity IS NOT NULL; -
Verify if the view is writable:
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWSWHERE TABLE_NAME='shipped_products';+------------------+--------------+ | TABLE_NAME | IS_UPDATABLE | +------------------+--------------+ | shipped_products | YES | +------------------+--------------+
-
Query the view:
SELECT product_id, SUM(quantity) FROM shipped_productsGROUP BY product_id;+------------+---------------+ | product_id | SUM(quantity) | +------------+---------------+ | 101 | 10 | | 102 | 13 | | 103 | 6 | | 104 | 7 | +------------+---------------+
-
Insert a row in the view, and then query the view and the underlying table:
INSERT INTO shipped_products VALUES (105, 10);SELECT product_id, SUM(quantity)FROM shipped_products GROUP BY product_id;+------------+---------------+ | product_id | SUM(quantity) | +------------+---------------+ | 105 | 10 | | 101 | 10 | | 102 | 13 | | 103 | 6 | | 104 | 7 | +------------+---------------+
SELECT product_id, SUM(quantity)FROM orders GROUP BY product_id;+------------+---------------+ | product_id | SUM(quantity) | +------------+---------------+ | 105 | 10 | | 101 | 10 | | 102 | 13 | | 103 | 6 | | 104 | 7 | +------------+---------------+
A new row with
product_
asid 105
is inserted in both the view and the underlying table. -
Delete the row, and then query the view and the underlying table:
DELETE FROM shipped_products WHERE product_id = 105;SELECT product_id, SUM(quantity)FROM shipped_products GROUP BY product_id;+------------+---------------+ | product_id | SUM(quantity) | +------------+---------------+ | 101 | 10 | | 102 | 13 | | 103 | 6 | | 104 | 7 | +------------+---------------+
SELECT product_id, SUM(quantity)FROM orders GROUP BY product_id;+------------+---------------+ | product_id | SUM(quantity) | +------------+---------------+ | 101 | 10 | | 102 | 13 | | 103 | 6 | | 104 | 7 | +------------+---------------+
The row with
product_
asid 105
is deleted from both the view and the underlying table.
Related Topics
Last modified: June 4, 2025