SnowConvert AI - PostgreSQL - Built-in functions¶

Applies to¶

PostgreSQL
Greenplum
Netezza

Note

For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.

Aggregate Functions¶

Aggregate functions compute a single result value from a set of input values. (PostgreSQL Language Reference Aggregate Functions (https://www.postgresql.org/docs/12/functions-aggregate.html)).

PostgreSQL	Snowflake
AVG (https://www.postgresql.org/docs/12/functions-aggregate.html)	AVG Notes: PostgreSQL and Snowflake may show different precision/decimals due to data type rounding/formatting.
COUNT (https://www.postgresql.org/docs/12/functions-aggregate.html)	COUNT
MAX (https://www.postgresql.org/docs/12/functions-aggregate.html)	MAX
MEDIAN (https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/ref_guide-function-summary.html#topic31)	MEDIAN Notes: Snowflake does not allow the use of date types, while PostgreSQL does. (See SSC-FDM-PG0013).
MIN (https://www.postgresql.org/docs/12/functions-aggregate.html)	MIN
PERCENTILE_CONT (https://www.postgresql.org/docs/9.4/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE)	PERCENTILE_CONT
STDDEV/STDDEV_SAMP (https://www.postgresql.org/docs/12/functions-aggregate.html) (expression)	STDDEV/STDDEV_SAMP (expression)
STDDEV_POP (https://www.postgresql.org/docs/12/functions-aggregate.html) (expression)	STDDEV_POP (expression)
SUM (https://www.postgresql.org/docs/12/functions-aggregate.html)	SUM
VARIANCE/VAR_SAMP (https://www.postgresql.org/docs/12/functions-aggregate.html) (expression)	VARIANCE/VAR_SAMP (expression)
VAR_POP (https://www.postgresql.org/docs/12/functions-aggregate.html) (expression)	VAR_POP (expression)

Conditional expressions¶

PostgreSQL	Snowflake
COALESCE (https://www.postgresql.org/docs/12/functions-conditional.html) ( value [, …] )	COALESCE ( expression, expression, … )
GREATEST (https://www.postgresql.org/docs/12/functions-conditional.html) ( value [, …] )	GREATEST_IGNORE_NULLS ( <expr1> [, <expr2> … ] )
LEAST (https://www.postgresql.org/docs/12/functions-conditional.html) ( value [, …] )	LEAST_IGNORE_NULLS ( <expr1> [, <expr2> … ])
NULLIF (https://www.postgresql.org/docs/12/functions-conditional.html)	NULLIF Notes: PostgreSQL’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. (PostgreSQL Language Reference Data type formatting functions (https://www.postgresql.org/docs/12/functions-formatting.html)).

PostgreSQL	Snowflake
TO_CHAR (https://www.postgresql.org/docs/12/functions-formatting.html)	TO_CHAR Notes: Snowflake’s support for this function is partial (see SSC-EWI-PG0005).
TO_DATE (https://www.postgresql.org/docs/12/functions-formatting.html)	TO_DATE Notes: Snowflake’s `TO_DATE` fails on invalid dates like ‘20010631’ (June has 30 days), unlike PostgreSQL’s lenient `TO_DATE`. Use `TRY_TO_DATE` in Snowflake to handle these cases by returning NULL. (see SSC-EWI-PG0005, SSC-FDM-0032).

PostgreSQL

Snowflake

TO_CHAR (https://www.postgresql.org/docs/12/functions-formatting.html)

TO_CHAR

Notes: Snowflake’s support for this function is partial (see SSC-EWI-PG0005).

TO_DATE (https://www.postgresql.org/docs/12/functions-formatting.html)

TO_DATE

Notes: Snowflake’s TO_DATE fails on invalid dates like ‘20010631’ (June has 30 days), unlike PostgreSQL’s lenient TO_DATE. Use TRY_TO_DATE in Snowflake to handle these cases by returning NULL. (see SSC-EWI-PG0005, SSC-FDM-0032).

Date and time functions¶

PostgreSQL	Snowflake
AT TIME ZONE ‘timezone’ (https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)	CONVERT_TIMEZONE ( <source_tz> , <target_tz> , <source_timestamp_ntz> ) CONVERT_TIMEZONE ( <target_tz> , <source_timestamp> ) Notes: PostgreSQL defaults to UTC; the Snowflake function requires explicit UTC specification. Therefore, it will be added as the target timezone.
CURRENT_DATE (https://www.postgresql.org/docs/8.2/functions-datetime.html)	CURRENT_DATE()
DATE_PART/PGDATE_PART (https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)	DATE_PART Notes: this function is partially supported by Snowflake. (See SSC-EWI-PGOOO5).
DATE_TRUNC (https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)	DATE_TRUNC Notes: Invalid date part formats are translated to Snowflake-compatible formats.
TO_TIMESTAMP (https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)	TO_TIMESTAMP
EXTRACT (https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)	EXTRACT Notes: Part-time or Date time supported: DAY, DOW, DOY, EPOCH, HOUR, MINUTE, MONTH, QUARTER, SECOND, WEEK, YEAR.
TIMEZONE (https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)	CONVERT_TIMEZONE

Note

PostgreSQL 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.

JSON Functions¶

PostgreSQL	Snowflake
JSON_EXTRACT_PATH_TEXT (https://www.postgresql.org/docs/9.3/functions-json.html)	JSON_EXTRACT_PATH_TEXT Notes: PostgreSQL treats newline, tab, and carriage return characters literally; Snowflake interprets them. A JSON literal and dot-separated path are required to access nested objects in the Snowflake function. Paths with spaces in variables must be quoted.

PostgreSQL

Snowflake

JSON_EXTRACT_PATH_TEXT (https://www.postgresql.org/docs/9.3/functions-json.html)

JSON_EXTRACT_PATH_TEXT

Notes:

PostgreSQL treats newline, tab, and carriage return characters literally; Snowflake interprets them.
A JSON literal and dot-separated path are required to access nested objects in the Snowflake function.
Paths with spaces in variables must be quoted.

Math functions¶

PostgreSQL	Snowflake
ACOS (https://www.postgresql.org/docs/12/functions-math.html)	ACOS
ASIN (https://www.postgresql.org/docs/12/functions-math.html)	ASIN
ATAN (https://www.postgresql.org/docs/12/functions-math.html)	ATAN
ATAN2 (https://www.postgresql.org/docs/12/functions-math.html)	ATAN2
CBRT (https://www.postgresql.org/docs/12/functions-math.html)	CBRT
CEIL/CEILING (https://www.postgresql.org/docs/12/functions-math.html)	CEIL
COS (https://www.postgresql.org/docs/12/functions-math.html)	COS
COT (https://www.postgresql.org/docs/12/functions-math.html)	COT
DEGREES (https://www.postgresql.org/docs/12/functions-math.html)	DEGREES
LN (https://www.postgresql.org/docs/12/functions-math.html)	LN
EXP (https://www.postgresql.org/docs/12/functions-math.html)	EXP
FLOOR (https://www.postgresql.org/docs/12/functions-math.html)	FLOOR
LOG (https://www.postgresql.org/docs/12/functions-math.html)	LOG
MOD (https://www.postgresql.org/docs/12/functions-math.html)	MOD
PI (https://www.postgresql.org/docs/12/functions-math.html)	PI
POWER/POW (https://www.postgresql.org/docs/12/functions-math.html)	POWER/POW
RADIANS (https://www.postgresql.org/docs/12/functions-math.html)	RADIANS
RANDOM (https://www.postgresql.org/docs/12/functions-math.html)	RANDOM
ROUND (https://www.postgresql.org/docs/12/functions-math.html)	ROUND
SIN (https://www.postgresql.org/docs/12/functions-math.html)	SIN
SIGN (https://www.postgresql.org/docs/12/functions-math.html)	SIGN
SQRT (https://www.postgresql.org/docs/12/functions-math.html)	SQRT
TAN (https://www.postgresql.org/docs/12/functions-math.html)	TAN
TRUNC (https://www.postgresql.org/docs/12/functions-math.html)	TRUNC

Note

PostgreSQL and Snowflake results may differ in scale.

String functions¶

String functions process and manipulate character strings or expressions that evaluate to character strings. (PostgreSQL Language Reference String functions (https://www.postgresql.org/docs/12/functions-string.html)).

PostgreSQL	Snowflake
ASCII (https://www.postgresql.org/docs/12/functions-string.html)	ASCII
BTRIM (https://www.postgresql.org/docs/12/functions-string.html)	TRIM
CHAR_LENGTH (https://www.postgresql.org/docs/12/functions-string.html)	LENGTH
CHARACTER_LENGTH (https://www.postgresql.org/docs/12/functions-string.html)	LENGTH
CHR (https://www.postgresql.org/docs/9.1/functions-string.html)	CHR
CONCAT (https://www.postgresql.org/docs/12/functions-string.html)	CONCAT
INITCAP (https://www.postgresql.org/docs/12/functions-string.html)	INITCAP
LEFT/RIGHT (https://www.postgresql.org/docs/12/functions-string.html)	LEFT/RIGHT
LOWER (https://www.postgresql.org/docs/12/functions-string.html)	LOWER
OCTET_LENGTH (https://www.postgresql.org/docs/12/functions-string.html)	OCTET_LENGTH Notes: the results may vary between platforms (See SSC-FDM-PG0013).
QUOTE_IDENT (https://www.postgresql.org/docs/12/functions-string.html) (string)	CONCAT (‘”’, string, ‘”’)
REGEXP_REPLACE (https://www.postgresql.org/docs/12/functions-string.html)	REGEXP_REPLACE Notes: This function includes a `parameters` argument that enables the user to interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect, represented by the `p` value, this is removed to avoid any issues. (See SSC-EWI-0009, SC-FDM-0032, SSC-FDM- PG0011).
REPEAT (https://www.postgresql.org/docs/12/functions-string.html)	REPEAT
REPLACE (https://www.postgresql.org/docs/12/functions-string.html)	REPLACE
REVERSE (https://www.postgresql.org/docs/12/functions-string.html)	REVERSE
SPLIT_PART (https://www.postgresql.org/docs/12/functions-string.html)	SPLIT_PART Notes: Snowflake and PostgreSQL handle SPLIT_PART differently with case-insensitive collations.
STRPOS (https://www.postgresql.org/docs/12/functions-string.html) (string, substring )	POSITION ( <expr1> IN <expr> )
SUBSTRING (https://www.postgresql.org/docs/12/functions-string.html)	SUBSTRING Notes: Snowflake partially supports this function. PostgreSQL’s `SUBSTRING`, with a non-positive `start_position`, calculates `start_position + number_characters` (returning ‘’ if the result is non-positive). Snowflake’s behavior differs.
TRANSLATE (https://www.postgresql.org/docs/12/functions-string.html)	TRANSLATE
TRIM (https://www.postgresql.org/docs/12/functions-string.html)	TRIM Notes: PostgreSQL uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.
UPPER (https://www.postgresql.org/docs/12/functions-string.html)	UPPER

Window functions¶

PostgreSQL	Snowflake
AVG (https://www.postgresql.org/docs/9.4/functions-aggregate.html)	AVG Notes: AVG rounding/formatting can vary by data type between PostgreSQL and Snowflake.
COUNT (https://www.postgresql.org/docs/9.4/functions-aggregate.html)	COUNT
DENSE_RANK (https://www.postgresql.org/docs/current/functions-window.html)	DENSE_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
FIRST_VALUE (https://www.postgresql.org/docs/current/functions-window.html)	FIRST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get `ORDER BY <expr>.`
LAG (https://www.postgresql.org/docs/current/functions-window.html)	LAG
LAST_VALUE (https://www.postgresql.org/docs/current/functions-window.html)	LAST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get `ORDER BY <expr>`.
LEAD (https://www.postgresql.org/docs/current/functions-window.html)	LEAD Notes: PostgreSQL allows constant or expression offsets; Snowflake allows only constant offsets.
NTH_VALUE (https://www.postgresql.org/docs/current/functions-window.html)	NTH_VALUE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
NTILE (https://www.postgresql.org/docs/current/functions-window.html)	NTILE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`. (See SSC-FDM-PG0013).
PERCENT_RANK (https://www.postgresql.org/docs/current/functions-window.html)	PERCENT_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.
PERCENTILE_CONT (https://www.postgresql.org/docs/9.4/functions-aggregate.html)	PERCENTILE_CONT Notes: Rounding varies between platforms.
PERCENTILE_DISC (https://www.postgresql.org/docs/9.4/functions-aggregate.html)	PERCENTILE_DISC
RANK (https://www.postgresql.org/docs/current/functions-window.html)	RANK
ROW_NUMBER (https://www.postgresql.org/docs/current/functions-window.html)	ROW_NUMBER Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with `ORDER BY 1`.

SnowConvert AI - PostgreSQL - Built-in functions¶

Applies to¶

Aggregate Functions¶

Conditional expressions¶

Data type formatting functions¶

Date and time functions¶

JSON Functions¶

Math functions¶

String functions¶

Window functions¶

Related EWIs¶