CREATE VIEW
Translation from PostgreSQL to Snowflake
Applies to
Description
This command creates a view in a database, which is run every time the view is referenced in a query.
For more information, please refer to CREATE VIEW
documentation.
Grammar Syntax
CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW <name> [ ( <column_name> [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS <query>
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Code Examples
[OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE]
This syntax is fully supported in Snowflake.
Input Code:
CREATE OR REPLACE VIEW view1 AS
SELECT
product_id,
SUM(quantity) AS sum_quantity
FROM
table1
GROUP BY
product_id;
CREATE TEMPORARY RECURSIVE VIEW view2 AS
SELECT
product_id,
SUM(quantity) AS sum_quantity
FROM
table1
GROUP BY
product_id;
Output Code:
CREATE OR REPLACE VIEW view1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "postgresql", "convertedOn": "05/14/2025", "domain": "no-domain-provided" }}'
AS
SELECT
product_id,
SUM(quantity) AS sum_quantity
FROM
table1
GROUP BY
product_id;
CREATE TEMPORARY RECURSIVE VIEW view2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "postgresql", "convertedOn": "05/14/2025", "domain": "no-domain-provided" }}'
AS
SELECT
product_id,
SUM(quantity) AS sum_quantity
FROM
table1
GROUP BY
product_id;
WITH CHECK CLAUSE
This WITH CHECK CLAUSE clause on a view enforces that any data inserted or updated through the view must satisfy the view's defining conditions. LOCAL checks only the current view's conditions, while CASCADED checks conditions of the view and all underlying views. It prevents creating rows that are invisible through the view and cannot be used with recursive views.
This syntax is not supported in Snowflake.
Input Code:
CREATE VIEW updatable_products AS
SELECT id, name, price
FROM products
WHERE price > 0
WITH LOCAL CHECK OPTION;
Output Code:
CREATE VIEW updatable_products
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "postgresql", "convertedOn": "05/14/2025", "domain": "no-domain-provided" }}'
AS
SELECT id, name, price
FROM
products
WHERE price > 0;
WITH PARAMETERS OPTIONS
This WITH PARAMETERS OPTIONS allows setting optional properties for the view, such as how modifications through the view are checked (check_option) and whether to enforce row-level security (security_barrier).
This syntax is not supported in Snowflake.
Input Code:
CREATE VIEW large_orders WITH (security_barrier=true, check_option=local) AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 1000;
Output Code:
CREATE VIEW large_orders
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "postgresql", "convertedOn": "05/14/2025", "domain": "no-domain-provided" }}'
AS
SELECT order_id, customer_id, total_amount
FROM
orders
WHERE total_amount > 1000;
VALUES OPTION
This syntax is fully supported in Snowflake.
Input Code:
CREATE VIEW numbers_view (number_1) AS
VALUES (1,2), (2,2), (3,2), (4,2), (5,2);
Output Code:
CREATE VIEW numbers_view
AS
SELECT
*
FROM
(
VALUES (1,2), (2,2), (3,2), (4,2), (5,2)
) AS numbers_view (
number_1
);
Last updated