SnowConvert AI - PostgreSQL - CREATE VIEW¶

Translation from PostgreSQL to Snowflake

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 (https://www.postgresql.org/docs/current/sql-createview.html) 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 ]

Copy

Code Examples¶

[OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE]¶

Hint

This syntax is fully supported in Snowflake.

Input Code:¶

PostgreSQL¶

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;

Copy

Output Code:¶

Snowflake¶

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;

Copy

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

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

Copy

Output Code:

Snowflake

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;

Copy

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¶

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;

Copy

Output Code:¶

Snowflake¶

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;

Copy

VALUES OPTION¶

Hint

This syntax is fully supported in Snowflake.

Input Code:¶

PostgreSQL¶

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

Copy

Output Code:¶

Snowflake¶

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

Copy