SnowConvert AI - Redshift - Built-in functions¶
Note
For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.
Fonctions d’agrégation¶
Aggregate functions compute a single result value from a set of input values. (Redshift SQL Language Reference Aggregate Functions).
Redshift |
Snowflake |
---|---|
ANY_VALUE ( [ DISTINCT |
ALL ] expression ) |
AVG ( [ DISTINCT | ALL ] expression ) |
AVG ( [ DISTINCT ] expression) Notes: Redshift and Snowflake may show different precision/decimals due to data type rounding/formatting. |
Notes: Redshift’s DISTINCT ignores trailing spaces (“a “ = “a”); Snowflake’s does not. (See SSC-FDM-PG0013). |
|
Notes: Snowflake does not allow the use of date types, while Redshift does. (See SSC-FDM-PG0013). |
|
STDDEV/STDDEV_SAMP ( [ DISTINCT |
ALL ] expression) STDDEV_POP ( [ DISTINCT |
VARIANCE/VAR_SAMP ( [ DISTINCT |
ALL ] expression) VAR_POP ( [ DISTINCT |
Array Functions¶
Creates an array of the SUPER data type. (Redshift SQL Language Reference Array Functions).
Redshift |
Snowflake |
---|---|
ARRAY ( [ expr1 ] [ , expr2 [ , … ] ] ) |
( [ <expr1> ] [ , <expr2> [ , … ] ] ) |
ARRAY_CONCAT ( super_expr1, super_expr2 ) |
ARRAY_CAT ( <array1> , <array2> ) |
( super_expr1,super_expr2,.. ) |
ARRAY_FLATTEN ( <array> ) Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
GET_ARRAY_LENGTH ( super_expr ) |
ARRAY_SIZE ( <array> | <variant>) |
SPLIT_TO_ARRAY ( string,delimiter ) |
SPLIT (<string>, <separator>) Notes: Redshift allows missing delimiters; Snowflake requires them, defaulting to comma |
SUBARRAY ( super_expr, start_position, length ) |
ARRAY_SLICE ( <array> , <from> , <to> ) Notes: Function names and the second argument differ; adjust arguments for equivalence. |
Conditional expressions¶
Redshift |
Snowflake |
---|---|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
COALESCE ( expression, expression, … ) |
COALESCE ( expression, expression, … ) |
GREATEST ( value [, …] ) |
GREATEST_IGNORE_NULLS ( <expr1> [, <expr2> … ] ) |
LEAST ( value [, …] ) |
LEAST_IGNORE_NULLS ( <expr1> [, <expr2> … ]) |
NVL( expression, expression, … ) |
NVL ( expression, expression ) Notes: Redshift’s NVL accepts multiple arguments; Snowflake’s NVL accepts only two. To match Redshift behavior, NVL with more than two arguments is converted to COALESCE. |
Notes: Redshift’s NULLIF ignores trailing spaces in some string comparisons, unlike Snowflake. Therefore, the transformation adds RTRIM for equivalence. |
Data type formatting functions¶
Data type formatting functions provide an easy way to convert values from one data type to another. For each of these functions, the first argument is always the value to be formatted and the second argument contains the template for the new format. (Redshift SQL Language Reference Data type formatting functions).
Redshift |
Snowflake |
---|---|
Notes: Snowflake’s support for this function is partial (see SSC-EWI-0006). |
|
Notes: Snowflake’s |
Date and time functions¶
Redshift |
Snowflake |
---|---|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
CONVERT_TIMEZONE ( <source_tz> , <target_tz> , <source_timestamp_ntz> ) CONVERT_TIMEZONE ( <target_tz> , <source_timestamp> ) Notes: Redshift defaults to UTC; the Snowflake function requires explicit UTC specification. Therefore, it will be added as the target timezone. |
|
DATEADD/DATE_ADD ( datepart, interval, {date | time | timetz | timestamp} ) |
DATE_ADD ( <date_or_time_part>, <value>, <date_or_time_expr> ) Notes: Invalid date part formats are translated to Snowflake-compatible formats. |
Notes: Invalid date part formats are translated to Snowflake-compatible formats. |
|
Notes: this function is partially supported by Snowflake. (See SSC-EWI-OOO6). |
|
DATE_PART_YEAR (date) |
YEAR ( <date_or_timestamp_expr> ) Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
Notes: Invalid date part formats are translated to Snowflake-compatible formats. |
|
GETDATE() |
GETDATE() |
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
SYSDATE() |
|
EXTRACT |
Note
Redshift timestamps default to microsecond precision (6 digits); Snowflake defaults to nanosecond precision (9 digits). Adjust precision as needed using ALTER SESSION (e.g., ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';
). Precision loss may occur depending on the data type used.
Since some formats are incompatible with Snowflake, adjusting the account parameters DATE_INPUT_FORMAT or TIME_INPUT_FORMAT might maintain functional equivalence between platforms.
Fonctions de hachage¶
A hash function is a mathematical function that converts a numerical input value into another value. (Redshift SQL Language Reference Hash functions).
Redshift |
Snowflake |
---|---|
FNV_HASH (value [, seed]) |
HASH ( <expr> [ , <expr> … ] |
Fonctions JSON¶
Redshift |
Snowflake |
---|---|
Notes:
|
Math functions¶
Redshift |
Snowflake |
---|---|
DLOG10 (number) |
LOG (10, number) |
Note
Redshift and Snowflake results may differ in scale.
String functions¶
String functions process and manipulate character strings or expressions that evaluate to character strings. (Redshift SQL Language Reference String functions).
Redshift |
Snowflake |
---|---|
Notes: For negative lengths in |
|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
QUOTE_IDENT (string) |
CONCAT (“ »”, string, “ »”) |
Notes: This function includes a |
|
Notes: Certain special characters, the results may vary between platforms (See SSC-FDM-PG0013). |
|
Notes: Snowflake and Redshift handle SPLIT_PART differently with case-insensitive collations. |
|
STRPOS (string, substring ) |
POSITION ( <expr1> IN <expr> ) |
Notes: Snowflake partially supports this function. Redshift’s |
|
Notes: Redshift uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM. |
|
SUPER type information functions¶
Redshift |
Snowflake |
---|---|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
Window functions¶
Redshift |
Snowflake |
---|---|
Notes: AVG rounding/formatting can vary by data type between Redshift and Snowflake. |
|
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with |
|
Notes: Snowflake needs ORDER BY; missing clauses get |
|
Notes: Snowflake needs ORDER BY; missing clauses get |
|
Notes: Redshift allows constant or expression offsets; Snowflake allows only constant offsets. |
|
Notes: Redshift’s DISTINCT ignores trailing spaces (“a “ = “a”); Snowflake’s does not. (See SSC-FDM-PG0013). |
|
Notes: Snowflake does not allow the use of date types, while Redshift does. (See SSC-FDM-PG0013). |
|
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with |
|
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with |
|
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with |
|
Notes: Rounding varies between platforms. |
|
Notes: the results may vary between platforms (See SSC-FDM-PG0013). |
|
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with |
|
STDDEV |
|
VARIANCE |
Problèmes connus ¶
For more information about quoted identifiers in functions, click here.
IDENTITY¶
Description ¶
La fonction IDENTITY est une fonction de système qui opère sur une colonne spécifiée d’une table pour déterminer la valeur initiale de l’identité. Si la valeur initiale n’est pas disponible, elle prend par défaut la valeur fournie dans la fonction. Ceci sera traduit en séquence dans Snowflake.
Grammar Syntax ¶
"identity"(oid_id, oid_table_id, default)
Note
Cette fonction n’est plus prise en charge dans Redshift. Elle utilise la valeur par défaut pour définir l’identité et se comporte comme une colonne d’identité standard.
Modèles d’échantillons de sources¶
Code d’entrée :¶
Redshift¶
CREATE TABLE IF NOT EXISTS table_test
(
id integer,
inventory_combo BIGINT DEFAULT "identity"(850178, 0, '5,3'::text)
);
INSERT INTO table_test (id) VALUES
(1),
(2),
(3),
(4);
SELECT * FROM table_test;
Results¶
id |
inventory_combo |
---|---|
1 |
5 |
2 |
8 |
3 |
11 |
3 |
14 |
Code de sortie :
Snowflake¶
CREATE TABLE IF NOT EXISTS table_test
(
id integer,
inventory_combo BIGINT IDENTITY(5,3) ORDER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/13/2024", "domain": "test" }}';
INSERT INTO table_test (id) VALUES
(1),
(2),
(3),
(4);
SELECT * FROM
table_test;
Results¶
id |
inventory_combo |
---|---|
1 |
5 |
2 |
8 |
3 |
11 |
3 |
14 |
EWIs connexes¶
Il n’y a pas de problème connu.
TO_CHAR¶
Date function
Description¶
TO\CHAR convertit un horodatage ou une expression numérique en format de données de type chaîne de caractères. (Référence linguistique Redshift SQL fonction TO_CHAR fonction)
Avertissement
Cette fonction est partiellement prise en charge par Snowflake.
Pour plus d’informations sur les identificateurs cités dans les fonctions, cliquez ici.
Grammar Syntax¶
TO_CHAR(timestamp_expression | numeric_expression , 'format')
Modèles d’échantillons de sources¶
Code d’entrée :¶
Redshift¶
SELECT TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYYY'),
TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYY'),
TO_CHAR(timestamp '2009-12-31 23:15:59', 'TH'),
"to_char"(timestamp '2009-12-31 23:15:59', 'MON-DY-DD-YYYY HH12:MIPM'),
TO_CHAR(125.8, '999.99'),
"to_char"(125.8, '999.99');
Results¶
TO_CHAR |
TO_CHAR |
TO_CHAR |
TO_CHAR |
TO_CHAR |
---|---|---|---|---|
2009 |
009 |
DEC-THU-31-2009 11:15PM |
125,80 |
125,80 |
Code de sortie :¶
Snowflake¶
SELECT
TO_CHAR(timestamp '2009-12-31 23:15:59', 'YYYY'),
PUBLIC.YEAR_PART_UDF(timestamp '2009-12-31 23:15:59', 3),
TO_CHAR(timestamp '2009-12-31 23:15:59', 'TH') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - TH FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
PUBLIC.MONTH_SHORT_UDF(timestamp '2009-12-31 23:15:59', 'uppercase') || '-' || PUBLIC.DAYNAME_SHORT_UDF(timestamp '2009-12-31 23:15:59', 'uppercase') || TO_CHAR(timestamp '2009-12-31 23:15:59', '-DD-YYYY HH12:MI') || PUBLIC.MERIDIAN_INDICATORS_UDF(timestamp '2009-12-31 23:15:59', 'uppercase'),
TO_CHAR(125.8, '999.99'),
TO_CHAR(125.8, '999.99');
Results¶
TO_CHAR |
TO_CHAR |
---|---|
2009 |
Déc-Jeu-31-2009 11 :15PM |
Problèmes connus ¶
Aucun problème n’a été constaté.
EWIs connexes¶
SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
Pour les valeurs temporelles¶
Translation specification for the TO_CHAR function when transforming date or timestamp values to string
Description¶
Les chaînes de format suivantes s’appliquent à des fonctions telles que TO_CHAR. Ces chaînes peuvent contenir des séparateurs de date (tels que “
-
”, “/
” ou “:
”) ainsi que les « dateparts » et « timeparts » suivants. (Page de référence des chaînes de format datetime de Redshift)
Grammar Syntax¶
TO_CHAR (timestamp_expression, 'format')
La table suivante spécifie le mappage de chaque élément de format à Snowflake :
Redshift |
Snowflake |
---|---|
|
|
|
|
|
|
|
Pris en charge directement |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Notes: For this UDF to work correctly the Snowflake session parameter |
|
|
|
|
|
Pris en charge directement |
|
|
|
Pris en charge directement |
|
Pris en charge directement |
|
Pris en charge directement |
|
|
|
|
|
|
|
|
|
Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result. |
|
+00 Notes: According to the redshift documentation, all timestamp with time zone are stored in UTC, which causes this format element to return a fixed result. |
|
|
|
Notes: This is a PostgreSQL template pattern modifier for « spell mode », however it does nothing on Redshift, so it is removed from the output. |
|
Notes: This is another template pattern modifier for « fixed format », however it has no use on the TO_CHAR function so it is removed. |
Modèles d’échantillons de sources¶
Transformation directe des éléments de format (pas de fonctions/UDFs)¶
Le résultat est conservé sous la forme d’une fonction unique TO_CHAR
Redshift¶
Query¶
SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH:MI:SS.MS') AS col1;
Result¶
+----------------------+
|col1 |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+
Snowflake¶
Query¶
SELECT TO_CHAR('2013-10-03 13:50:15.456871'::TIMESTAMP, 'DD/MM/YY HH12:MI:SS.FF3') AS col1;
Result¶
+----------------------+
|col1 |
+----------------------+
|03/10/13 01:50:15.456 |
+----------------------+
Transformation de format à l’aide de fonctions/UDFs¶
Le résultat est une concaténation de plusieurs fonctions intégrées TOCHAR, UDFs et Snowflake qui génèrent la représentation équivalente sous forme de chaîne de la valeur de l’heure de la date
Redshift¶
Query¶
SELECT TO_CHAR(DATE '2025-07-05', '"Today is " Month DAY DD, "it belongs to the week " IW') AS result;
Result¶
+-------------------------------------------------------------+
|result |
+-------------------------------------------------------------+
|Today is July SATURDAY 05, it belongs to the week 27 |
+-------------------------------------------------------------+
Snowflake¶
Query¶
SELECT
'Today is ' ||
TO_CHAR(DATE '2025-07-05', ' ') ||
PUBLIC.FULL_MONTH_NAME_UDF(DATE '2025-07-05', 'firstOnly') ||
' ' ||
PUBLIC.DAYNAME_LONG_UDF(DATE '2025-07-05', 'uppercase') ||
TO_CHAR(DATE '2025-07-05', ' DD, ') ||
'it belongs to the week ' ||
TO_CHAR(DATE '2025-07-05', ' ') ||
WEEKISO(DATE '2025-07-05') AS result;
Result¶
+-------------------------------------------------------------+
|result |
+-------------------------------------------------------------+
|Today is July SATURDAY 05, it belongs to the week 27 |
+-------------------------------------------------------------+
Texte cité¶
Les éléments de format dans le texte entre guillemets doubles sont ajoutés à la sortie directement sans les interpréter, les guillemets doubles échappés sont transformés en leur équivalent échappé dans Snowflake.
Redshift¶
Query¶
SELECT
TO_CHAR(DATE '2025-01-16', 'MM "TESTING DD" DD') AS result1,
TO_CHAR(DATE '2025-01-16', 'MM TESTING \\"DD\\" DD') AS result2,
TO_CHAR(DATE '2025-01-16', 'MM "TESTING \\"DD\\"" DD') AS result3;
Result¶
+-----------------+-------------------+-------------------+
|result1 |result2 |result3 |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+
Snowflake¶
Query¶
SELECT
TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING DD' || TO_CHAR(DATE '2025-01-16', ' DD') AS result1,
TO_CHAR(DATE '2025-01-16', 'MM TEST') || PUBLIC.ISO_YEAR_PART_UDF(DATE '2025-01-16', 1) || TO_CHAR(DATE '2025-01-16', 'NG ""DD"" DD') AS result2,
TO_CHAR(DATE '2025-01-16', 'MM ') || 'TESTING "DD"' || TO_CHAR(DATE '2025-01-16', ' DD') AS result3;
Result¶
+-----------------+-------------------+-------------------+
|result1 |result2 |result3 |
+-----------------+-------------------+-------------------+
|01 TESTING DD 16 |01 TEST5NG "16" 16 |01 TESTING "DD" 16 |
+-----------------+-------------------+-------------------+
Problèmes connus¶
Les modificateurs de modèles ne sont pas pris en charge¶
Les modificateurs de modèles de format suivants :
FM (mode remplissage)
TH et th (suffixe du nombre ordinal en majuscules et minuscules)
TM (mode traduction)
Are not supported, including them in a format will generate SSC-EWI-0006
Code d’entrée :
SELECT TO_CHAR(CURRENT_DATE, 'FMMonth'),
TO_CHAR(CURRENT_DATE, 'DDTH'),
TO_CHAR(CURRENT_DATE, 'DDth'),
TO_CHAR(CURRENT_DATE, 'TMMonth');
Code de sortie :
SELECT
TO_CHAR(CURRENT_DATE(), 'FM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - FMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDTH') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - DDTH FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'DDth') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - DDth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!,
TO_CHAR(CURRENT_DATE(), 'TM') || PUBLIC.FULL_MONTH_NAME_UDF(CURRENT_DATE(), 'firstOnly') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - TMMonth FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!;
Paramètre du format transmis par la variable
Lorsque le paramètre du format est transmis comme une variable au lieu d’un littéral de chaîne, la transformation des éléments de format ne peut pas être appliquée, un avertissement FDM sera ajouté aux utilisations de la fonction à ce sujet.
Code d’entrée :
SELECT TO_CHAR(d, 'YYYY/MM/DD'),
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);
Code de sortie :
SELECT TO_CHAR(d, 'YYYY/MM/DD'),
--** SSC-FDM-0032 - PARAMETER 'format_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
TO_CHAR(d, f)
FROM (SELECT TO_DATE('2001-01-01','YYYY-MM-DD') as d, 'DD/MM/YYYY' as f);
EWIs connexes¶
SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
[SSC-FDM-0032](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0032) : le paramètre n’est pas une valeur littérale, la transformation n’a pas pu être entièrement appliquée