SnowConvert AI - Redshift - Built-in functions¶
참고
For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.
집계 함수¶
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 |
참고
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.
해시 함수¶
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> … ] |
JSON 함수¶
Redshift |
Snowflake |
---|---|
Notes:
|
Math functions¶
Redshift |
Snowflake |
---|---|
DLOG10 (number) |
LOG (10, number) |
참고
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 |
알려진 문제 ¶
For more information about quoted identifiers in functions, click here.
IDENTITY¶
설명 ¶
IDENTITY 함수는 테이블의 지정된 열에서 작업을 수행하여 ID의 초기 값을 결정하는 시스템 함수입니다. 초기값을 사용할 수 없는 경우 기본값은 함수에 제공된 값으로 설정됩니다. 이것은 Snowflake의 시퀀스로 변환됩니다.
문법 구문 ¶
"identity"(oid_id, oid_table_id, default)
참고
이 함수는 더 이상 Redshift에서 지원되지 않습니다. 기본값을 사용하여 ID를 정의하고 표준 ID 열처럼 작동합니다.
샘플 소스 패턴¶
입력 코드:¶
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 |
출력 코드:
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¶
알려진 문제는 없습니다.
TO_CHAR¶
Date function
설명¶
TO\CHAR 타임스탬프 또는 숫자 식을 문자 문자열 데이터 형식으로 변환합니다. (Redshift SQL Language Reference TO_CHAR 함수)
경고
이 함수는 Snowflake 에서 부분적으로 지원됩니다.
함수의 따옴표로 묶인 식별자에 대한 자세한 내용은 여기를 클릭하십시오.
문법 구문¶
TO_CHAR(timestamp_expression | numeric_expression , 'format')
샘플 소스 패턴¶
입력 코드:¶
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 |
출력 코드:¶
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 |
Dec-Thu-31-2009 11:15PM |
알려진 문제 ¶
문제가 발견되지 않았습니다.
관련 EWIs¶
SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
날짜/시간 값의 경우¶
Translation specification for the TO_CHAR function when transforming date or timestamp values to string
설명¶
다음 형식 문자열은 TO_CHAR 과 같은 함수에 적용됩니다. 이러한 문자열에는 날짜/시간 구분 기호(예: ‘
-
’, ‘/
’ 또는 ‘:
’)와 다음 “dateparts” 및 “timeparts”가 포함될 수 있습니다. (Redshift 날짜/시간 형식 문자열 참조 페이지)
문법 구문¶
TO_CHAR (timestamp_expression, 'format')
다음 테이블은 각 형식 요소의 Snowflake에 대한 매핑을 지정합니다.
Redshift |
Snowflake |
---|---|
|
|
|
|
|
|
|
직접 지원 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Notes: For this UDF to work correctly the Snowflake session parameter |
|
|
|
|
|
직접 지원 |
|
|
|
직접 지원 |
|
직접 지원 |
|
직접 지원 |
|
|
|
|
|
|
|
|
|
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. |
샘플 소스 패턴¶
직접 형식 요소 변환(함수 없음/UDFs)¶
결과는 단일 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 |
+----------------------+
함수를 사용한 형식 변환/UDFs¶
결과는 여러 개의 TO_CHAR, UDFs 및 Snowflake 기본 제공 함수를 연결하여 날짜/시간 값의 동등한 문자열 표현을 생성합니다
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 |
+-------------------------------------------------------------+
인용된 텍스트¶
큰따옴표로 묶인 텍스트의 형식 요소는 해석하지 않고 바로 출력에 추가되며, 이스케이프된 큰따옴표는 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 |
+-----------------+-------------------+-------------------+
Known Issues¶
지원되지 않는 템플릿 패턴 수정자¶
다음 형식의 템플릿 수정자를 사용할 수 있습니다.
FM (채우기 모드)
TH 및 th(대문자 및 소문자 서수 접미사)
TM (변환 모드)
Are not supported, including them in a format will generate SSC-EWI-0006
입력 코드:
SELECT TO_CHAR(CURRENT_DATE, 'FMMonth'),
TO_CHAR(CURRENT_DATE, 'DDTH'),
TO_CHAR(CURRENT_DATE, 'DDth'),
TO_CHAR(CURRENT_DATE, 'TMMonth');
출력 코드:
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. ***/!!!;
변수를 통해 전달된 형식 매개 변수
형식 매개 변수가 문자열 리터럴 대신 변수로 전달되면 형식 요소의 변환을 적용할 수 없으며, 함수 사용에 대한 FDM 경고가 추가됩니다.
입력 코드:
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);
출력 코드:
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¶
SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.
SSC-FDM-0032: 매개 변수가 리터럴 값이 아니므로 변환을 완전히 적용할 수 없습니다