CREATE VIEW

Translation from PostgreSQL to Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• PostgreSQL

• Greenplum

• Netezza

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:

PostgreSQL

IN -> PostgreSQL_01.sql

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:

Snowflake

OUT -> PostgreSQL_01.sql

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:

PostgreSQL

IN -> PostgreSQL_02.sql

CREATE VIEW updatable_products AS
    SELECT id, name, price
    FROM products
    WHERE price > 0
WITH LOCAL CHECK OPTION;

Output Code:

Snowflake

OUT -> PostgreSQL_02.sql

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:

PostgreSQL

IN -> PostgreSQL_03.sql

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:

Snowflake

OUT -> PostgreSQL_03.sql

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:

PostgreSQL

IN -> PostgreSQL_03.sql

CREATE VIEW numbers_view (number_1) AS
    VALUES (1,2), (2,2), (3,2), (4,2), (5,2);

Output Code:

Snowflake

OUT -> PostgreSQL_04.sql

CREATE VIEW numbers_view
AS
SELECT
*
FROM
(
        VALUES (1,2), (2,2), (3,2), (4,2), (5,2)
) AS numbers_view (
        number_1
);