SnowConvert: PostgreSQL Functional Differences

SSC-FDM-PG0001

Severity

Low

Description

The FOUND property in PostgreSQL indicates whether the most recent query returned any results. This property can be modified by several SQL commands including INSERT, UPDATE, DELETE, MERGE, SELECT INTO, PERFORM, FETCH, and FOR loops. For more information, please refer to the PostgreSQL documentation.

In Snowflake, there is no direct equivalent for this property in any scenario.

  • INSERT

  • UPDATE

  • DELETE

  • MERGE

The code will be converted to the Snowflake SQLFOUND property (Here is the documentation), which functions similarly to PostgreSQL’s FOUND property.

  • SELECT INTO - Retrieves data from a query and stores it into variables

  • PERFORM - Executes a query and discards the results

  • FETCH - Retrieves rows from a cursor into variables

The code will create a custom User-Defined Function (UDF) named IS_FOUND_UDF that replicates the functionality of PostgreSQL’s FOUND property.

The SQLFOUND variable only updates its value when a query modifies at least one row in the database. If the most recent query doesn’t modify any rows, the value of SQLFOUND remains unchanged.

The IS_FOUND_UDF function returns TRUE only when the statement returns one or more rows. If the statement returns no rows, the function returns FALSE.

SQLFOUND Example
 INSERT INTO SampleTable (SampleColumn1)
VALUES ('SampleValue0.1');
Copy

Since this query modifies a table, SQLFOUND provides functionality that most closely matches PostgreSQL’s behavior.

IS_FOUND_UDF Example
 SELECT SampleColumn FROM SampleTable;
Copy

The IS_FOUND_UDF() function most closely matches PostgreSQL’s behavior, as it returns a row without making any changes to the data.

IS_FOUND_UDF Source Code
 CREATE OR REPLACE FUNCTION FOUND_UDF() 
RETURNS BOOLEAN
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
SELECT (count(*) != 0) FROM TABLE(result_scan(last_query_id()))
$$;
Copy

Code Example

Insert Statement:
 -- Found property used with INSERT statement.
CREATE OR REPLACE FUNCTION FoundUsingInsertFunction()
RETURNS BOOLEAN
AS
$$
  BEGIN
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');
    RETURN FOUND;
  END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with INSERT statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingInsertFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
  BEGIN
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');
    RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
  END;
$$;
Copy
Update Statement
 -- Found property used with UPDATE statement.
CREATE OR REPLACE FUNCTION FoundUsingUpdateFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        UPDATE SampleTable 
        SET SampleColumn1 = 'SampleValue0.1' 
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with UPDATE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingUpdateFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        UPDATE SampleTable
        SET SampleColumn1 = 'SampleValue0.1'
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;
Copy
Delete Statement
 -- Found property used with DELETE statement.
CREATE OR REPLACE FUNCTION FoundUsingDeleteFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        DELETE FROM SampleTable 
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with DELETE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingDeleteFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        DELETE FROM
            SampleTable
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;
Copy
Merge Statement
 -- Found property used with MERGE statement.
CREATE OR REPLACE FUNCTION FoundUsingMergeFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        MERGE INTO SampleTableB B
        USING (SELECT * FROM SampleTableA) A
        ON B.SampleColumn1 = A.SampleColumn2
        WHEN MATCHED THEN DELETE;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with MERGE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingMergeFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        MERGE INTO SampleTableB B
        USING (SELECT * FROM SampleTableA) A
        ON B.SampleColumn1 = A.SampleColumn2
        WHEN MATCHED THEN DELETE !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MergeStatement' NODE ***/!!!;
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;
Copy
Select Into Statement
 -- Found property used with SELECT INTO statement.
CREATE OR REPLACE FUNCTION FoundUsingSelectIntoFunction()
RETURNS BOOLEAN
AS
$$
    DECLARE 
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO SampleNumber;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with SELECT INTO statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    DECLARE
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO
            : SampleNumber;
        RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;
Copy
Execute Statement:
 -- Found property used with PERFORM statement.
CREATE OR REPLACE FUNCTION FoundUsingPerformFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        PERFORM 1;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with PERFORM statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingPerformFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        SELECT
            1;
        RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;
Copy
Fetch Statement:
 -- Found property used with FETCH statement.
CREATE OR REPLACE FUNCTION FoundUsingFetchFunction ()
RETURNS BOOLEAN
AS
$$
    DECLARE
        SampleRow VARCHAR;
        SampleCursor CURSOR FOR SELECT EmptyColumn FROM EmptyTable;
    BEGIN
        OPEN SampleCursor;
        FETCH SampleCursor;
        CLOSE SampleCursor;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;
Copy
 -- Found property used with FETCH statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingFetchFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
   DECLARE
       SampleRow VARCHAR;
       SampleCursor CURSOR FOR SELECT EmptyColumn FROM
           EmptyTable;
   BEGIN
       OPEN SampleCursor;
       !!!RESOLVE EWI!!! /*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
       FETCH SampleCursor;
       CLOSE SampleCursor;
       RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
   END;
$$;
Copy

Best Practices

SSC-FDM-PG0002

Description

This warning appears because the bpchar (blank-padded char) data type may behave differently than varchar in Snowflake. While both data types can store up to “n” characters and use storage space based on the actual data length, they handle trailing spaces differently. In bpchar, trailing spaces are not stored, whereas Snowflake’s varchar preserves them.

To avoid storing unnecessary blank spaces, you can use the RTRIM function. However, be aware that in some cases, this may not provide exactly the same functionality as the original code.

Code Example

Input Code:
 CREATE TABLE table1 (
    col1 BPCHAR, 
    col2 BPCHAR(20)
);
Copy
SELECT 'Y'::BPCHAR;

SELECT 'Y   '::BPCHAR(20);
SELECT COL1::BPCHAR(20) FROM tbl;
Copy
Generated Code:
 CREATE TABLE table1 (
    col1 VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/,
    col2 VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';
Copy
 SELECT 'Y':: VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;

SELECT
    RTRIM( 'Y   ') :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;

SELECT
    RTRIM( COL1) :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
FROM
    tbl;
Copy

Best Practices

  • The rtrim function removes trailing spaces, which can help resolve storage differences. While explicit cast handles this automatically, you may need to use rtrim manually in other situations. Learn more about rtrim in the Snowflake RTRIM documentation.

  • For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-FDM-PG0003

Description

This warning indicates that converting data from bytea format to binary reduces the maximum size limit significantly, from 1 gigabyte (GB) to 8 megabytes (MB).

Code Example

Input Code:
CREATE TABLE tbl(
    col BYTEA
);
Copy
Generated Code:
 CREATE TABLE tbl (
    col BINARY /*** SSC-FDM-PG0003 - BYTEA CONVERTED TO BINARY. SIZE LIMIT REDUCED FROM 1GB TO 8MB ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';
Copy

Best Practices

SSC-FDM-PG0004

Description

The format of the date output depends on two factors: the Timestamp type and the timestamp_output_format setting. For more details, please refer to the Snowflake documentation.

Code Example

Input Code:
 CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
);
Copy
Generated Code:
 CREATE TABLE table1 (
    dt_update TIMESTAMP_NTZ DEFAULT CAST(
    --** SSC-FDM-PG0004 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. **
    CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';
Copy

Examples

Creating Tables: A Basic Example

This example demonstrates how to create a table in your database.

Input Code:

 CREATE TABLE sample2 (
    platform_id integer NOT NULL,
    dt_update timestamp with time zone DEFAULT clock_timestamp()
);
    
insert into postgres.public.sample2 (platform_id) values (1);
   
select *, clock_timestamp() from postgres.public.sample2;
Copy
 platform_id|dt_update                    |clock_timestamp              |
-----------+-----------------------------+-----------------------------+
          1|2023-02-05 22:47:34.275 -0600|2023-02-05 23:16:15.754 -0600|
Copy
Generated Code:
 CREATE TABLE sample2 (
    platform_id integer NOT NULL,
    dt_update TIMESTAMP_TZ DEFAULT CAST(
--** SSC-FDM-PG0004 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. **
CURRENT_TIMESTAMP() AS TIMESTAMP_TZ)
);

insert into postgres.public.sample2 (platform_id) values (1);
ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';

select *,
CURRENT_TIMESTAMP(3)
from
postgres.public.sample2;
Copy
 PLATFORM_ID   DT_UPDATE	                        CURRENT_TIMESTAMP(3)
1	      2023-02-05 20:52:30.082000000	2023-02-05 21:20:31.593
Copy

Using SELECT with clock_timestamp() - Example

Input Code
 select clock_timestamp();
Copy
 clock_timestamp        |
-----------------------+
2023-02-05 23:24:13.740|
Copy
Output Code
 ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
select
    CURRENT_TIMESTAMP(3);
Copy
 CURRENT_TIMESTAMP(3)
2023-02-05 21:29:24.258
Copy

Best Practices

SSC-FDM-PG0006

Description

Setting multiple schemas in a search path is not supported in Snowflake. For more information about schema usage, please refer to the Snowflake documentation.

Code Example

Input Code:
 SET SEARCH_PATH TO schema1, schema2, schema3;
Copy
Generated Code:
 --** SSC-FDM-PG0006 - SET SEARCH PATH WITH MULTIPLE SCHEMAS IS NOT SUPPORTED IN SNOWFLAKE **
USE SCHEMA schema1 /*, schema2, schema3*/;
Copy

Best Practices

SSC-FDM-PG0007

Severity

Low

Description

To remove a comment in PostgreSQL, you use NULL. However, in Snowflake, you should use an empty string ('') instead. Both methods achieve the same result of removing the comment.

Code Example

Input Code:
 COMMENT ON TABLE mytable IS NULL;
Copy
Generated Code:
 COMMENT ON TABLE mytable IS '' /*** SSC-FDM-PG0007 - NULL IS CONVERTED TO '' AND MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;
Copy

Best Practices

SSC-FDM-PG0008

Description

The SELECT INTO statement is not available in Snowflake. Instead, you can use CREATE TABLE AS to achieve similar functionality. Additionally, Snowflake automatically maintains transaction logs for all tables to ensure data integrity and recovery capabilities. As a result, tables with the UNLOGGED option are not supported in Snowflake.

Code Example

Input Code:
 select column1
      into UNLOGGED NewTable
      from oldTable;
Copy
Output Code:
 CREATE TABLE IF NOT EXISTS NewTable AS
      select column1
--      --** SSC-FDM-PG0008 - SELECT INTO UNLOGGED TABLES ARE NOT SUPPORTED BY SNOWFLAKE. **
--            into UNLOGGED NewTable
            from
            oldTable;
Copy

Best Practices

SSC-FDM-PG0009

Description

Snowflake’s sequence numbers may have gaps between them. While the numbers will always increase (or decrease if using a negative step size), they may not be consecutive.

Code Example

Input Code:
 SELECT nextval('seq1');
Copy
Output Code:
 SELECT seq1.nextval /*** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. ***/;
Copy

Recommendations

SSC-FDM-PG0010

Description

The PostgreSQL bitwise shift operators << and >> are translated to Snowflake’s BITSHIFTLEFT and BITSHIFTRIGHT functions. Please note that these functions may produce different results in Snowflake compared to PostgreSQL due to differences in expression handling.

Code Example

Input Code:
 SELECT 1 << 127 AS resultShiftedLeft, 16 >> 32 AS resultShiftedRight;
Copy

resultShiftedLeft

resultShiftedRight

-2147483648

16

Output Code:
 SELECT
--** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTLEFT BITWISE FUNCTION **
BITSHIFTLEFT( 1, 127) AS resultShiftedLeft,
--** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTRIGHT BITWISE FUNCTION **
BITSHIFTRIGHT( 16, 32) AS resultShiftedRight;
Copy

resultShiftedLeft

resultShiftedRight

-170141183460469231731687303715884105728

0

Recommendations

SSC-FDM-PG0011

Description

This message appears when pattern matching includes COLLATE specifications in its arguments. Since Snowflake’s regular expression function does not support COLLATE specifications, the COLLATE clause must be removed. Please note that removing this clause may lead to different results compared to the source database.

Code Example

Input Code:
 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE CASE_INSENSITIVE,
col2 VARCHAR(30) COLLATE CASE_SENSITIVE);

INSERT INTO collateTable values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
col1 SIMILAR TO 'Hello%' as ci,
col2 SIMILAR TO 'Hello%' as cs
FROM collateTable;
Copy

CI

CS

TRUE

FALSE

Output Code:

 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE 'en-ci',
col2 VARCHAR(30) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO collateTable
values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col1, ''), 'Hello.*', 's') as ci,
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col2, ''), 'Hello.*', 's') as cs
FROM
collateTable;
Copy

CI

CS

FALSE

FALSE

Recommendations

  • To achieve functional equivalence in these scenarios, you can manually add these parameters to the function:

| Parameter | Description | | ——— | ————————- | | c | Case-sensitive matching | | i | Case-insensitive matching |

SSC-FDM-PG0012

Description

In Redshift, when a variable is marked with NOT NULL, attempting to assign a null value will cause a runtime error. However, Snowflake does not support this constraint. During the migration process, the NOT NULL clause is removed, which means assigning NULL values to variables will be allowed and won’t generate errors during execution.

Code Example

Input Code:
 CREATE OR REPLACE PROCEDURE variable_Not_Null()
LANGUAGE plpgsql
AS $$
DECLARE
    v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;
Copy

[22004] ERROR: A NULL value cannot be assigned to the variable “v_notnull” because it was declared with a NOT NULL constraint.

Output Code:
 CREATE OR REPLACE PROCEDURE variable_Not_Null ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    --** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
    v_notnull VARCHAR DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;
Copy

This operation will execute successfully in Snowflake.

Recommendations

  • Check if the procedure’s logic prevents this variable from being assigned a NULL value.

  • For additional assistance, contact us at snowconvert-support@snowflake.com