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

PostgreSQLSnowflake
AVG

AVG

Notes: PostgreSQL and Snowflake may show different precision/decimals due to data type rounding/formatting.

COUNT COUNT
MAXMAX
MEDIAN

MEDIAN

Notes: Snowflake does not allow the use of date types, while PostgreSQL does. (See SSC-FDM-PG0013).

MINMIN
PERCENTILE_CONTPERCENTILE_CONT
STDDEV/STDDEV_SAMP (expression)STDDEV/STDDEV_SAMP (expression)
STDDEV_POP (expression)STDDEV_POP (expression)
SUMSUM
VARIANCE/VAR_SAMP (expression)VARIANCE/VAR_SAMP (expression)
VAR_POP (expression)VAR_POP (expression)

Conditional expressions

PostgreSQLSnowflake
COALESCE ( value [, …] )COALESCE ( expression, expression, … )
GREATEST ( value [, …] )

GREATEST_IGNORE_NULLS ( <expr1> [, <expr2> … ] )

LEAST ( value [, …] )LEAST_IGNORE_NULLS ( &lt;expr1> [, &lt;expr2> … ])
NULLIF

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

PostgreSQLSnowflake
TO_CHAR

TO_CHAR

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

TO_DATE

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

PostgreSQLSnowflake
AT TIME ZONE ‘timezone’

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_DATECURRENT_DATE()
DATE_PART/PGDATE_PART

DATE_PART

Notes: this function is partially supported by Snowflake. (See SSC-EWI-PG0005).

DATE_TRUNC

DATE_TRUNC

Notes: Invalid date part formats are translated to Snowflake-compatible formats.

TO_TIMESTAMPTO_TIMESTAMP
EXTRACT

EXTRACT

Notes: Part-time or Date time supported: DAY, DOW, DOY, EPOCH, HOUR, MINUTE, MONTH, QUARTER, SECOND, WEEK, YEAR.

TIMEZONECONVERT_TIMEZONE

Note

PostgreSQL timestamps default to microsecond precision (6 digits); Snowflake defaults to nanosecond precision (9 digits). Adjust precision as needed using ALTER SESSION (for example, 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

PostgreSQLSnowflake
JSON_EXTRACT_PATH_TEXT

JSON_EXTRACT_PATH_TEXT

Notes:

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

Math functions

PostgreSQLSnowflake
ACOSACOS
ASINASIN
ATANATAN
ATAN2ATAN2
CBRTCBRT
CEIL/CEILINGCEIL
COSCOS
COTCOT
DEGREESDEGREES
LNLN
EXPEXP
FLOORFLOOR
LOGLOG
MODMOD
PIPI
POWER/POWPOWER/POW
RADIANSRADIANS
RANDOMRANDOM
ROUNDROUND
SINSIN
SIGNSIGN
SQRTSQRT
TANTAN
TRUNCTRUNC

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

PostgreSQLSnowflake
ASCIIASCII
BTRIMTRIM
“char” (internal type)LEFT(expr, 1). Both the function call form "char"(expr) and the explicit cast form expr::"char" are converted.
CHAR_LENGTHLENGTH
CHARACTER_LENGTHLENGTH
CHRCHR
CONCATCONCAT
INITCAPINITCAP
LEFT/RIGHTLEFT/RIGHT
LOWERLOWER
MD5MD5
OCTET_LENGTH

OCTET_LENGTH

Notes: the results may vary between platforms (See SSC-FDM-PG0013).

QUOTE_IDENT (string)CONCAT (‘”’, string, ‘”’)
REGEXP_REPLACE

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

REPEATREPEAT
REPLACEREPLACE
REVERSEREVERSE
SPLIT_PART

SPLIT_PART

Notes: Snowflake and PostgreSQL handle SPLIT_PART differently with case-insensitive collations.

STRPOS (string, substring )POSITION ( &lt;expr1> IN &lt;expr> )
SUBSTRING

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.

TRANSLATETRANSLATE
TRIM

TRIM

Notes: PostgreSQL uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.

UPPERUPPER

Sequence functions

Sequence manipulation functions provide methods to access and use sequence generators. (PostgreSQL Language Reference Sequence functions).

PostgreSQLSnowflake
NEXTVAL (‘sequence_name’)

sequence_name.NEXTVAL

Notes: Both nextval('seq') and nextval('seq'::regclass) forms are supported. (See SSC-FDM-PG0009).

Window functions

PostgreSQLSnowflake
AVG

AVG

Notes: AVG rounding/formatting can vary by data type between PostgreSQL and Snowflake.

COUNTCOUNT
DENSE_RANK

DENSE_RANK

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

FIRST_VALUE

FIRST_VALUE

Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.

LAGLAG
LAST_VALUE

LAST_VALUE

Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.

LEAD

LEAD

Notes: PostgreSQL allows constant or expression offsets; Snowflake allows only constant offsets.

NTH_VALUE

NTH_VALUE

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

NTILE

NTILE

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1. (See SSC-FDM-PG0013).

PERCENT_RANK

PERCENT_RANK

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

PERCENTILE_CONT

PERCENTILE_CONT

Notes: Rounding varies between platforms.

PERCENTILE_DISCPERCENTILE_DISC
RANKRANK
ROW_NUMBER

ROW_NUMBER

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

  • SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied
  • SSC-FDM-PG0013: Function syntactically supported by Snowflake but may have functional differences.
  • SSC-EWI-0009: Regexp_Substr Function only supports POSIX regular expressions.
  • SSC-FDM-PG0011: The use of the COLLATE column constraint has been disabled for this pattern-matching condition.