SnowConvert AI - Différences fonctionnelles PostgreSQL¶

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

SELECT SampleColumn FROM SampleTable;

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()))
$$;

-- Found property used with INSERT statement.
CREATE OR REPLACE PROCEDURE FoundUsingInsertProcedure()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Insert into SampleTable
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');

    SELECT FOUND;
END;
$$;

-- Found property used with INSERT statement.
CREATE OR REPLACE PROCEDURE FoundUsingInsertProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
BEGIN
    -- Insert into SampleTable
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');

    SELECT
        SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
END;
$$;

 -- Found property used with UPDATE statement.
CREATE OR REPLACE PROCEDURE FoundUsingUpdateProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        UPDATE SampleTable 
        SET SampleColumn1 = 'SampleValue0.1' 
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT FOUND;        
    END;
$$;

 -- Found property used with UPDATE statement.
CREATE OR REPLACE PROCEDURE FoundUsingUpdateProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        UPDATE SampleTable
        SET SampleColumn1 = 'SampleValue0.1'
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT
        SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with DELETE statement.
CREATE OR REPLACE PROCEDURE FoundUsingDeleteProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        DELETE FROM SampleTable 
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT FOUND;
    END;
$$;

 -- Found property used with DELETE statement.
CREATE OR REPLACE PROCEDURE FoundUsingDeleteProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        DELETE FROM
            SampleTable
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT
        SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with MERGE statement.
CREATE OR REPLACE PROCEDURE FoundUsingMergeProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        MERGE INTO SampleTableB B
        USING (SELECT * FROM SampleTableA) A
        ON B.SampleColumn1 = A.SampleColumn2
        WHEN MATCHED THEN DELETE;
        SELECT FOUND;
    END;
$$;

 -- Found property used with MERGE statement.
CREATE OR REPLACE PROCEDURE FoundUsingMergeProcedure ()
RETURNS VARCHAR
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 ***/!!!;
        SELECT
        SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with SELECT INTO statement.
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoProcedure()
LANGUAGE plpgsql
AS
$$
    DECLARE 
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO SampleNumber;
        SELECT FOUND;
    END;
$$;

 -- Found property used with SELECT INTO statement.
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO
        : SampleNumber;
        SELECT
        FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with PERFORM statement.
CREATE OR REPLACE PROCEDURE FoundUsingPerformProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        PERFORM 1;
        RETURN FOUND;
    END;
$$;

 -- Found property used with PERFORM statement.
CREATE OR REPLACE PROCEDURE FoundUsingPerformProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
    SELECT
        1;
    RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with FETCH statement.
CREATE OR REPLACE PROCEDURE FoundUsingFetchProcedure ()
LANGUAGE plpgsql
AS
$$
    DECLARE
        SampleRow VARCHAR;
        SampleCursor CURSOR FOR SELECT EmptyColumn FROM EmptyTable;
    BEGIN
        OPEN SampleCursor;
        FETCH SampleCursor;
        CLOSE SampleCursor;
        SELECT FOUND;
    END;
$$;

 -- Found property used with FETCH statement.
CREATE OR REPLACE PROCEDURE FoundUsingFetchProcedure ()
RETURNS VARCHAR
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;
        SELECT
        FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

CREATE TABLE table1 (
    col1 BPCHAR, 
    col2 BPCHAR(20)
);

SELECT 'Y'::BPCHAR;
SELECT 'Y   '::BPCHAR(20);
SELECT COL1::BPCHAR(20) FROM tbl;

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" }}';

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;

CREATE TABLE tbl(
    col BYTEA
);

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" }}';

CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
);

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" }}';

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;

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;

select clock_timestamp();

ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
select
    CURRENT_TIMESTAMP(3);

CREATE UNLOGGED TABLE TABLE1 (
   COL1 integer 
);

CREATE
--       --** SSC-FDM-PG0005 - UNLOGGED TABLE IS NOT SUPPORTED IN SNOWFLAKE, DATA WRITTEN MAY HAVE DIFFERENT PERFORMANCE. **
--       UNLOGGED
                TABLE TABLE1 (
COL1 integer
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "greenplum",  "convertedOn": "04/21/2025",  "domain": "test" }}';

 SET SEARCH_PATH TO schema1, schema2, schema3;

 --** SSC-FDM-PG0006 - SET SEARCH PATH WITH MULTIPLE SCHEMAS IS NOT SUPPORTED IN SNOWFLAKE **
USE SCHEMA schema1 /*, schema2, schema3*/;

 COMMENT ON TABLE mytable IS NULL;

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

select column1
      into UNLOGGED NewTable
      from oldTable;

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;

 SELECT nextval('seq1');

 SELECT seq1.nextval /*** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. ***/;

CREATE TABLE someTable (
  intCol INTEGER,
  smallIntCol SMALLINT,
  varbyteCol VARBYTE,
  incrementValue INTEGER
)
;

SELECT 
  intCol << incrementValue,
  smallIntCol >> incrementValue,
  varbyteCol << incrementValue
FROM someTable;


SELECT missingCol << incrementValue FROM missingTable;

CREATE TABLE someTable (
  intCol INTEGER,
  smallIntCol SMALLINT,
  varbyteCol BINARY,
  incrementValue INTEGER
)
;

SELECT
  PUBLIC.INTEGER_BITSHIFTLEFT_UDF(
  intCol, MOD(incrementValue, 32), 32),
  BITSHIFTRIGHT(
  smallIntCol, MOD(incrementValue, 16)),
  BITSHIFTLEFT(
  varbyteCol, incrementValue)
FROM
  someTable;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "missingTable" **
SELECT
  --** SSC-FDM-PG0010 - DATATYPE OF THE LEFT OPERAND COULD NOT BE DETERMINED. RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTLEFT BITWISE FUNCTION **
  BITSHIFTLEFT( missingCol, incrementValue) FROM
  missingTable;

 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;

 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": "postgresql",  "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;

 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;
$$;

SELECT
    LISTAGG(skill) WITHIN GROUP (ORDER BY skill) OVER (PARTITION BY
    employee_name) AS employee_skills
FROM
    employees;

SELECT
--** SSC-FDM-PG0013 - FUNCTION SYNTACTICALLY SUPPORTED BY SNOWFLAKE BUT MAY HAVE FUNCTIONAL DIFFERENCES **
LISTAGG(skill) WITHIN GROUP (ORDER BY skill) OVER (PARTITION BY
employee_name) AS employee_skills
FROM
    employees;

CREATE TABLE PSEUDOTYPES
(
  COL1 UNKNOWN
)

CREATE TABLE PSEUDOTYPES (
  COL1 TEXT /*** SSC-FDM-PG0014 -  UNKNOWN PSEUDOTYPE TRANSFORMED TO TEXT TYPE ***/
)

 \set ON_ERROR_STOP TRUE

 ----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. COMMAND OPTION **
--\set ON_ERROR_STOP TRUE

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter ARRAY /*** SSC-FDM-PG0016 - STRONGLY TYPED ARRAY 'INTEGER[]' TRANSFORMED TO ARRAY WITHOUT TYPE CHECKING ***/,
    schedule ARRAY /*** SSC-FDM-PG0016 - STRONGLY TYPED ARRAY 'TEXT[][]' TRANSFORMED TO ARRAY WITHOUT TYPE CHECKING ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "06/03/2025",  "domain": "no-domain-provided" }}';

CREATE OR REPLACE FUNCTION log_user_activity(
    user_id_param INT,
    action_param TEXT
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO user_activity_log (user_id, action, activity_timestamp)
    VALUES (user_id_param, action_param, NOW());
END;
$$ LANGUAGE plpgsql;

--** SSC-FDM-PG0017 - USER DEFINED FUNCTION THAT RETURNS VOID WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE log_user_activity (
user_id_param INT,
    action_param TEXT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "07/23/2025",  "domain": "no-domain-provided" }}'
AS $$
BEGIN
    INSERT INTO user_activity_log (user_id, action, activity_timestamp)
    VALUES (:user_id_param, : action_param, CURRENT_TIMESTAMP());
END;
$$;

ANALYZE customers (first_name, last_name)

----** SSC-FDM-PG0018 - ANALYZE STATEMENT IS COMMENTED OUT, WHICH IS NOT APPLICABLE IN SNOWFLAKE. **
--ANALYZE customers (first_name, last_name)