# FlinkSQL → PostgreSQL 18+ Function Mapping (Non‑Identical Functions)

| **FlinkSQL Signature**                                                                              | **Mapping in PostgreSQL 18+**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|-----------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `LOG2(numeric)`                                                                                     | PostgreSQL has no `LOG2`. Use `LOG(2, numeric)` or `LN(numeric) / LN(2)`.                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `LOG(numeric2)` / `LOG(numeric1, numeric2)`                                                         | Flink 1‑arg `LOG(x)` = natural log; PostgreSQL 1‑arg `LOG(x)` = base‑10 log. Map 1‑arg as `LN(x)`. Two‑arg form maps directly to `LOG(base, x)`.                                                                                                                                                                                                                                                                                                                                                                        |
| `E()`                                                                                               | No `E()` function. Use `EXP(1)` or literal `2.718281828459045`.                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `RAND()`                                                                                            | Use `RANDOM()`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `RAND(INT)`                                                                                         | PostgreSQL seed uses `SETSEED(double)` (range −1..1). Emulate: `SELECT setseed(seed / 2147483647.0); SELECT random();`.                                                                                                                                                                                                                                                                                                                                                                                                 |
| `RAND_INTEGER(INT)`                                                                                 | Use `FLOOR(RANDOM() * INT)::int` for `[0, INT)`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `RAND_INTEGER(INT1, INT2)`                                                                          | Emulate seed + range: `SELECT setseed(INT1 / 2147483647.0); SELECT FLOOR(RANDOM() * INT2)::int;`.                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `UUID()`                                                                                            | Use `uuidv4()` (alias of `gen_random_uuid()`). Cast to text if needed.                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `BIN(INT)`                                                                                          | Use `to_bin(INT)`. To strip leading zeros (to match Flink examples), apply: `REGEXP_REPLACE(to_bin(INT), '^0+(\\d)', '\\1')`.                                                                                                                                                                                                                                                                                                                                                                                           |
| `HEX(numeric)`                                                                                      | Cast to integer and use `to_hex(numeric::bigint)`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `HEX(string)`                                                                                       | Use `encode(convert_to(string, 'UTF8'), 'hex')`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `UNHEX(expr)`                                                                                       | Use `decode(expr, 'hex')` → `bytea`. Convert to text with `convert_from(decode(expr, 'hex'), 'UTF8')`.                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `TRUNCATE(numeric1, integer2)` / `TRUNCATE(numeric1)`                                               | PostgreSQL uses `TRUNC`. Map to `TRUNC(numeric1, integer2)` or `TRUNC(numeric1)`.                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `PRINTF(format[, obj]*)`                                                                            | Use `format(format, obj1, obj2, ...)`, PostgreSQL’s printf-style formatter.  [oai_citation:0‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                                      |
| `STARTSWITH(expr, startExpr)`                                                                       | Use `starts_with(expr, startExpr)` or the prefix operator `expr ^@ startExpr` (both return `boolean`).  [oai_citation:1‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                           |
| `ENDSWITH(expr, endExpr)`                                                                           | No dedicated function. Emulate with `right(expr, char_length(endExpr)) = endExpr` or `expr LIKE '%'                                                                                                                                                                                                                                                                                                                                                                                                                     || endExpr`.  [oai_citation:2‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com) |
| `REGEXP_EXTRACT(string1, string2[, integer])`                                                       | Use regex functions: <br>• Full match (Flink `integer` omitted or `0`): `regexp_substr(string1, string2)` <br>• Capture group *n* (`integer >= 1`): `(regexp_match(string1, string2))[integer]` (returns `text` or `NULL`).  [oai_citation:3‡postgresql.org](https://www.postgresql.org/docs/current/functions-matching.html?utm_source=chatgpt.com)                                                                                                                                                                    |
| `REGEXP_EXTRACT_ALL(str, regex[, extractIndex])`                                                    | Use `regexp_matches` + aggregation: <br>• For capture group *k* (default 1):<br>`SELECT array_agg(m[k]) FROM regexp_matches(str, regex, 'g') AS m;` <br>• For “whole match” (Flink `extractIndex = 0`), use group 1 when your pattern has no explicit groups, or adjust pattern accordingly.  [oai_citation:4‡postgresql.org](https://www.postgresql.org/docs/current/functions-matching.html?utm_source=chatgpt.com)                                                                                                   |
| `DECODE(binary, string)` (charset decode)                                                           | Flink: decode bytes to string with a charset. <br>PostgreSQL: use `convert_from(binary, string)` where `binary` is `bytea` and `string` is encoding name, e.g. `convert_from(my_bytea, 'UTF8')`.  [oai_citation:5‡postgresql.org](https://www.postgresql.org/docs/current/functions-binarystring.html?utm_source=chatgpt.com)                                                                                                                                                                                           |
| `ENCODE(string1, string2)` (charset encode)                                                         | Flink: encode string to binary with charset. <br>PostgreSQL: use `convert_to(string1, string2)` to get `bytea` in that encoding, e.g. `convert_to('hello', 'UTF8')`.  [oai_citation:6‡postgresql.org](https://www.postgresql.org/docs/current/functions-binarystring.html?utm_source=chatgpt.com)                                                                                                                                                                                                                       |
| `FROM_BASE64(string)`                                                                               | Use `convert_from(decode(string, 'base64'), 'UTF8')` to get text from Base64.  [oai_citation:7‡postgresql.org](https://www.postgresql.org/docs/current/functions-binarystring.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                              |
| `TO_BASE64(string)`                                                                                 | Use `encode(convert_to(string, 'UTF8'), 'base64')` to get Base64 text.  [oai_citation:8‡postgresql.org](https://www.postgresql.org/docs/current/functions-binarystring.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                                     |
| `INSTR(string1, string2)`                                                                           | Use `strpos(string1, string2)` (or `position(string2 in string1)`; note reversed arg order). Both return 1-based index, 0 if not found.  [oai_citation:9‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                          |
| `LOCATE(string1, string2[, integer])`                                                               | 2-arg form (search from start): `strpos(string2, string1)`.<br>3-arg form (search from position `integer`):<br>`CASE WHEN integer <= 0 OR integer > char_length(string2) THEN 0`<br>`     ELSE`<br>`       CASE WHEN strpos(substring(string2 FROM integer), string1) = 0 THEN 0`<br>`            ELSE strpos(substring(string2 FROM integer), string1) + integer - 1`<br>`       END`<br>`END`  [oai_citation:10‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com) |
| `REGEXP(string1, string2)`                                                                          | Flink returns `TRUE` if any substring matches. <br>PostgreSQL: use `regexp_like(string1, string2)` (SQL-standard) or `string1 ~ string2`. Both return `boolean`.  [oai_citation:14‡postgresql.org](https://www.postgresql.org/docs/current/functions-matching.html?utm_source=chatgpt.com)                                                                                                                                                                                                                              |
| `SPLIT_INDEX(string1, string2, integer1)`                                                           | Flink uses 0-based index. PostgreSQL `split_part` is 1-based: <br>`split_part(string1, string2, integer1 + 1)` (returns empty string if out of range).  [oai_citation:15‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                          |
| `STR_TO_MAP(string1[, string2, string3])`                                                           | Represent as `jsonb` or `hstore`. For example, with pair delimiter `string2` and key/value delimiter `string3`: <br>`SELECT jsonb_object_agg(kv[1], kv[2])`<br>`FROM regexp_split_to_table(string1, string2) AS pair`<br>`CROSS JOIN LATERAL regexp_split_to_array(pair, string3) AS kv;` <br>This yields a `jsonb` map `{key: value, ...}` analogous to Flink’s map.  [oai_citation:16‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com)                           |
| `JSON_QUOTE(string)`                                                                                | Use `to_json(string)::text`, which returns a properly quoted JSON string literal (including surrounding quotes and escaped characters).  [oai_citation:17‡postgresql.org](https://www.postgresql.org/docs/current/functions-matching.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                       |
| `ELT(index, expr[, exprs]*)`                                                                        | Use array indexing: `ARRAY[expr, exprs...][index]`. PostgreSQL arrays are 1-based and out-of-range subscripts return `NULL`, which matches ELT’s semantics for invalid indexes.  [oai_citation:19‡postgresql.org](https://www.postgresql.org/docs/current/arrays.html?utm_source=chatgpt.com)                                                                                                                                                                                                                           |
| `DATE string`                                                                                       | `CAST(string AS date)` or `string::date` (expects `YYYY-MM-DD`; use `to_date(string, 'YYYY-MM-DD')` if you want explicit format control).                                                                                                                                                                                                                                                                                                                                                                               |
| `TIME string`                                                                                       | `CAST(string AS time)` or `string::time` (expects a time string such as `HH:MI:SS`).                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| `TIMESTAMP string`                                                                                  | `CAST(string AS timestamp)` or `string::timestamp` (PostgreSQL will parse `YYYY-MM-DD HH:MI:SS[.MS]` by default).                                                                                                                                                                                                                                                                                                                                                                                                       |
| `LOCALTIME`                                                                                         | `LOCALTIME(0)`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `LOCALTIMESTAMP`                                                                                    | `LOCALTIMESTAMP(3)`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `CURRENT_TIME`                                                                                      | `CURRENT_TIME(0)`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `CURRENT_DATE`                                                                                      | `CURRENT_DATE`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `CURRENT_TIMESTAMP`                                                                                 | `CURRENT_TIMESTAMP(3)`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `DAYOFWEEK(date)`                                                                                   | `EXTRACT(DOW FROM date) + 1` (Flink/MySQL return 1–7 with Sunday=1; PostgreSQL `EXTRACT(DOW)` returns 0–6 with Sunday=0).  [oai_citation:2‡CommandPrompt Inc.](https://www.commandprompt.com/education/how-to-get-a-day-from-a-date-in-postgresql/?utm_source=chatgpt.com)                                                                                                                                                                                                                                              |
| `FLOOR(timepoint TO timeintervalunit)`                                                              | `date_trunc('unit', timepoint)` – e.g. `date_trunc('minute', ts)` to floor to the minute.  [oai_citation:3‡postgresql.org](https://www.postgresql.org/docs/current/functions-datetime.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                      |
| `CEIL(timepoint TO timeintervalunit)`                                                               | For a given unit, e.g. minute: `date_trunc('minute', ts) + INTERVAL '1 minute' * (ts > date_trunc('minute', ts))::int` (general pattern: floor with `date_trunc`, then add one unit if there is a remainder).                                                                                                                                                                                                                                                                                                           |
| `DATE_FORMAT(timestamp, string)`                                                                    | `to_char(timestamp, pg_format)` where `pg_format` is the PostgreSQL format string corresponding to the Java `SimpleDateFormat` pattern. Example: `DATE_FORMAT(ts, 'yyyy-MM-dd')` → `to_char(ts, 'YYYY-MM-DD')`.  [oai_citation:4‡Stack Overflow](https://stackoverflow.com/questions/12052705/date-format-in-postgresql?utm_source=chatgpt.com)                                                                                                                                                                         |
| `TIMESTAMPADD(timeintervalunit, interval, timepoint)`                                               | Add an appropriate interval: e.g. `TIMESTAMPADD(DAY, n, ts)` → `ts + (n                                                                                                                                                                                                                                                                                                                                                                                                                                                 || ' day')::interval`; `TIMESTAMPADD(HOUR, n, ts)` → `ts + (n || ' hour')::interval`. |
| `TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)`                                              | Compute `timepoint2 - timepoint1` and scale `EXTRACT(EPOCH ...)`. Examples: seconds → `EXTRACT(EPOCH FROM (t2 - t1))`; minutes → `EXTRACT(EPOCH FROM (t2 - t1)) / 60`; hours → `/ 3600`; days → `/ 86400`. For months/years you typically use `age(t2, t1)` and extract fields.  [oai_citation:5‡Tiger Data](https://www.tigerdata.com/learn/understanding-postgres-extract-function?utm_source=chatgpt.com)                                                                                                            |
| `CONVERT_TZ(string1, string2, string3)`                                                             | Parse and shift time zones with `AT TIME ZONE`. Example: `CONVERT_TZ('1970-01-01 00:00:00','UTC','America/Los_Angeles')` → `(timestamp '1970-01-01 00:00:00' AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles'`.  [oai_citation:6‡postgresql.org](https://www.postgresql.org/docs/current/functions-datetime.html?utm_source=chatgpt.com)                                                                                                                                                                          |
| `FROM_UNIXTIME(numeric[, string])`                                                                  | Base value: `to_timestamp(numeric)` (epoch seconds → `timestamptz`). With format: `to_char(to_timestamp(numeric), pg_format)` – you must convert the MySQL/JAVA format to PostgreSQL’s `to_char` template.  [oai_citation:7‡postgresql.org](https://www.postgresql.org/docs/current/functions-formatting.html?utm_source=chatgpt.com)                                                                                                                                                                                   |
| `UNIX_TIMESTAMP()` / `unixTimestamp()`                                                              | `EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)::bigint` or `floor(EXTRACT(EPOCH FROM now()))`.  [oai_citation:8‡Neon](https://neon.com/postgresql/postgresql-date-functions/postgresql-now?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                              |
| `UNIX_TIMESTAMP(string1[, string2])` / `unixTimestamp(STRING1[, STRING2])`                          | Parse then take epoch: with format → `EXTRACT(EPOCH FROM to_timestamp(string1, pg_format))::bigint`; with default Flink format `yyyy-MM-dd HH:mm:ss` you can often use just `EXTRACT(EPOCH FROM string1::timestamp)` if the session time zone semantics match.  [oai_citation:9‡postgresql.org](https://www.postgresql.org/docs/current/functions-formatting.html?utm_source=chatgpt.com)                                                                                                                               |
| `TO_DATE(string1[, string2])` / `toDate(STRING1[, STRING2])`                                        | Two-arg: `to_date(string1, pg_format)` (PostgreSQL only supports the 2-argument form and its own format patterns). One-arg form can be approximated as `CAST(string1 AS date)` assuming `YYYY-MM-DD`.  [oai_citation:10‡Neon](https://neon.com/postgresql/postgresql-date-functions/postgresql-to_date?utm_source=chatgpt.com)                                                                                                                                                                                          |
| `TO_TIMESTAMP_LTZ(numeric[, precision])` / `toTimestampLtz(NUMERIC, PRECISION)`                     | Epoch seconds (precision=0): `to_timestamp(epoch_seconds)`; epoch milliseconds (precision=3): `to_timestamp(epoch_millis / 1000.0)`. Result is a `timestamptz`, which corresponds most closely to Flink’s `TIMESTAMP_LTZ`.  [oai_citation:11‡postgresql.org](https://www.postgresql.org/docs/current/functions-formatting.html?utm_source=chatgpt.com)                                                                                                                                                                  |
| `TO_TIMESTAMP_LTZ(string1[, string2[, string3]])` / `toTimestampLtz(STRING1[, STRING2[, STRING3]])` | Parse string to timestamptz using PostgreSQL’s `to_timestamp`, controlling time zone via session or `AT TIME ZONE`. Rough mapping: if `string3` is a time zone, `SET TIME ZONE string3; SELECT to_timestamp(string1, pg_format);` or, when the zone is embedded in the string, include it in the format mask (e.g. `to_timestamp(string1, 'YYYY-MM-DD HH24:MI:SS TZ')`).                                                                                                                                                |
| `TO_TIMESTAMP(string1[, string2])` / `toTimestamp(STRING1[, STRING2])`                              | Two-arg: `to_timestamp(string1, pg_format)::timestamp` (cast from `timestamptz` to `timestamp` to drop time zone). One-arg: `CAST(string1 AS timestamp)` or `string1::timestamp` if it’s already in a standard format.  [oai_citation:12‡postgresql.org](https://www.postgresql.org/docs/current/functions-formatting.html?utm_source=chatgpt.com)                                                                                                                                                                      |
| `IF(condition, true_value, false_value)`                                                            | `CASE WHEN condition THEN true_value ELSE false_value END`                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `IFNULL(input, null_replacement)` / `input.ifNull(nullReplacement)`                                 | `COALESCE(input, null_replacement)` (note that PostgreSQL’s type/nullability inference may not exactly match Flink’s, but semantics are “replace NULL by the second argument”).  [oai_citation:13‡postgresql.org](https://www.postgresql.org/docs/current/functions-conditional.html?utm_source=chatgpt.com)                                                                                                                                                                                                            |
| `IS_ALPHA(string)`                                                                                  | `string ~ '^[A-Za-z]+$'` (use POSIX regexp match operator `~`; adjust character class for locale/Unicode if needed).                                                                                                                                                                                                                                                                                                                                                                                                    |
| `IS_DECIMAL(string)`                                                                                | Either regex or a guarded cast, e.g. regex: `string ~ '^[+-]?[0-9]+(\\.[0-9]+)?$'`; guarded cast: `pg_input_is_valid(string, 'numeric')` in newer PostgreSQL versions to test if it can be cast to `numeric`.  [oai_citation:14‡Stack Overflow](https://stackoverflow.com/questions/77748853/postgresql-how-to-convert-text-date-to-show-in-the-format-of-yyyy-mm-dd?utm_source=chatgpt.com)                                                                                                                            |
| `IS_DIGIT(string)`                                                                                  | `string ~ '^[0-9]+$'`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| `BOOLEAN.?(VALUE1, VALUE2)`                                                                         | `CASE WHEN boolean_expression THEN value1 ELSE value2 END`                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `ELEMENT(array)`                                                                                    | Use subscripting plus a cardinality check: `CASE cardinality(a) WHEN 0 THEN NULL WHEN 1 THEN a[1] ELSE /* handle error */ NULL END`. Here `a` is the array expression; `cardinality(anyarray)` and `a[1]` are built-ins.                                                                                                                                                                                                                                                                                                |
| `CARDINALITY(map)`                                                                                  | PostgreSQL has no generic MAP type. If the map is stored as a JSONB object, use `jsonb_object_length(map_jsonb)` to get the number of key/value pairs.  [oai_citation:0‡SQL and PL/SQL](https://sqlandplsql.wordpress.com/2022/07/26/postgresql-json-functions-and-operators/?utm_source=chatgpt.com)                                                                                                                                                                                                                   |
| `map '[' key ']'`                                                                                   | For a JSONB object `map_jsonb` with text keys, use subscripting: `map_jsonb -> key::text` (JSONB value) or `map_jsonb ->> key::text` (text value).  [oai_citation:1‡PostgreSQL](https://www.postgresql.org/docs/current/datatype-json.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                      |
| `ARRAY_CONTAINS(haystack, needle)`                                                                  | Use `needle = ANY(haystack)` (boolean) or `haystack @> ARRAY[needle]` when element type matches. Both are built-in array operators.  [oai_citation:2‡Stack Overflow](https://stackoverflow.com/questions/40636406/how-to-get-distinct-array-elements-with-postgres?utm_source=chatgpt.com)                                                                                                                                                                                                                              |
| `ARRAY_DISTINCT(haystack)`                                                                          | Use `unnest` + `array_agg(DISTINCT ...)`, preserving first occurrence order via `WITH ORDINALITY`:<br>`SELECT array_agg(DISTINCT e ORDER BY min_ord)`<br>`FROM (SELECT e, MIN(ord) AS min_ord`<br>`      FROM unnest(haystack) WITH ORDINALITY AS t(e, ord)`<br>`      GROUP BY e) s;`                                                                                                                                                                                                                                  |
| `ARRAY_PREPEND(array, element)`                                                                     | PostgreSQL has `array_prepend(anyelement, anyarray)` (note argument order). So map `ARRAY_PREPEND(a, x)` to `array_prepend(x, a)`.  [oai_citation:3‡PostgreSQL](https://www.postgresql.org/docs/9.1/functions-array.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                        |
| `ARRAY_SLICE(array, start_offset[, end_offset])`                                                    | Basic positive offsets map to array slice syntax: `array[start_offset:end_offset]`. To emulate Flink negatives/zero, first normalize offsets: e.g.<br>`SELECT a[ l : r ]` where `l` and `r` are computed with `CASE` using `cardinality(a)` for negative/NULL values.                                                                                                                                                                                                                                                   |
| `ARRAY_UNION(array1, array2)`                                                                       | Concatenate then de-duplicate: `SELECT array_agg(DISTINCT e) FROM unnest(array1                                                                                                                                                                                                                                                                                                                                                                                                                                         || array2) AS t(e);`. (`||` is the array concatenation operator.)  [oai_citation:4‡Crunchy Data](https://www.crunchydata.com/blog/postgres-strings-to-arrays-and-back-again?utm_source=chatgpt.com) |
| `ARRAY_CONCAT(array1, ...)`                                                                         | Use array concatenation operator or `array_cat`: e.g. `array1                                                                                                                                                                                                                                                                                                                                                                                                                                                           || array2 || array3` (or nested `array_cat(array1, array2)`).  [oai_citation:5‡Crunchy Data](https://www.crunchydata.com/blog/postgres-strings-to-arrays-and-back-again?utm_source=chatgpt.com) |
| `ARRAY_EXCEPT(array1, array2)`                                                                      | Elements in `array1` but not in `array2`, without duplicates:<br>`SELECT array_agg(DISTINCT e ORDER BY ord)`<br>`FROM unnest(array1) WITH ORDINALITY AS t(e, ord)`<br>`WHERE NOT (e = ANY(array2));`                                                                                                                                                                                                                                                                                                                    |
| `ARRAY_REVERSE(haystack)`                                                                           | Use `unnest` with ordinality and reverse the order: `SELECT array_agg(e ORDER BY ord DESC) FROM unnest(haystack) WITH ORDINALITY AS t(e, ord);`                                                                                                                                                                                                                                                                                                                                                                         |
| `ARRAY_SORT(array[, ascending_order[, null_first]])`                                                | Sort via `unnest` + `array_agg` with `ORDER BY`. For example ascending with NULLS FIRST:<br>`SELECT array_agg(e ORDER BY e NULLS FIRST)`<br>`FROM unnest(array) AS t(e);`<br>To support `ascending_order`/`null_first` flags, drive the `ORDER BY` with `CASE` expressions based on those booleans.                                                                                                                                                                                                                     |
| `ARRAY_INTERSECT(array1, array2)`                                                                   | Elements common to both, without duplicates, preserving order from `array1`:<br>`SELECT array_agg(DISTINCT e ORDER BY ord)`<br>`FROM unnest(array1) WITH ORDINALITY AS t(e, ord)`<br>`WHERE e = ANY(array2);`                                                                                                                                                                                                                                                                                                           |
| `ARRAY_MAX(array)`                                                                                  | Use `unnest` + `max`: `SELECT max(e) FROM unnest(array) AS t(e);` (wrap in `CASE WHEN array IS NULL THEN NULL ...` if you must propagate NULL input explicitly).                                                                                                                                                                                                                                                                                                                                                        |
| `ARRAY_MIN(array)`                                                                                  | Analogous to `ARRAY_MAX`: `SELECT min(e) FROM unnest(array) AS t(e);`.                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `ARRAY_JOIN(array, delimiter[, nullReplacement])`                                                   | Use `array_to_string(anyarray, delimiter [, null_string])`. The third argument corresponds to `nullReplacement`. E.g. `array_to_string(array, delimiter, nullReplacement)`.  [oai_citation:6‡PostgreSQL](https://www.postgresql.org/docs/9.1/functions-array.html?utm_source=chatgpt.com)                                                                                                                                                                                                                               |
| `MAP_KEYS(map)`                                                                                     | For a JSONB map `m`: `SELECT array_agg(key)` from `jsonb_each(m)` **or** just `ARRAY(SELECT jsonb_object_keys(m));`. Both return `text[]` of keys (order not guaranteed, matching Flink).  [oai_citation:7‡SQL and PL/SQL](https://sqlandplsql.wordpress.com/2022/07/26/postgresql-json-functions-and-operators/?utm_source=chatgpt.com)                                                                                                                                                                                |
| `MAP_UNION(map1, ...)`                                                                              | For JSONB objects, use the `                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ||` operator, which merges objects and lets later keys overwrite earlier ones: `map1 || map2 || map3`. This matches the “later maps override earlier” behavior.  [oai_citation:8‡SQL and PL/SQL](https://sqlandplsql.wordpress.com/2022/07/26/postgresql-json-functions-and-operators/?utm_source=chatgpt.com) |
| `MAP_VALUES(map)`                                                                                   | For a JSONB object `m`: `SELECT array_agg(value)` from `jsonb_each(m)` → `ARRAY(SELECT value FROM jsonb_each(m));`. Result is `jsonb[]` (or cast as needed). Order is implementation-dependent, as in Flink.  [oai_citation:9‡SQL and PL/SQL](https://sqlandplsql.wordpress.com/2022/07/26/postgresql-json-functions-and-operators/?utm_source=chatgpt.com)                                                                                                                                                             |
| `MAP_ENTRIES(map)`                                                                                  | Represent entries as an array of composite key/value pairs or JSON objects. For JSONB `m` you can use, for example:<br>`SELECT ARRAY(SELECT (key, value) FROM jsonb_each(m))` (array of anonymous composite rows) **or**<br>`ARRAY(SELECT jsonb_build_object('key', key, 'value', value) FROM jsonb_each(m));`.  [oai_citation:10‡SQL and PL/SQL](https://sqlandplsql.wordpress.com/2022/07/26/postgresql-json-functions-and-operators/?utm_source=chatgpt.com)                                                         |
| `MAP_FROM_ARRAYS(array_of_keys, array_of_values)`                                                   | Build a JSONB object from two text arrays: `jsonb_object(array_of_keys::text[], array_of_values::text[])`. (Requires same length arrays, just like Flink.)  [oai_citation:11‡PostgreSQL](https://www.postgresql.org/docs/9.5/functions-json.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                                |
| `SPLIT(string, delimiter)`                                                                          | Use `string_to_array(string, delimiter [, null_string])`. E.g. `string_to_array(string, delimiter)` returns `text[]`. Note: in PostgreSQL an **empty** delimiter returns the whole string as a single element; to split into characters you pass `NULL` as delimiter instead.  [oai_citation:12‡PostgreSQL](https://www.postgresql.org/docs/9.1/functions-array.html?utm_source=chatgpt.com)                                                                                                                            |
| `SHA1(string)`                                                                                      | PostgreSQL core doesn’t expose `sha1(text)` directly. With the `pgcrypto` extension, use `encode(digest(string, 'sha1'), 'hex')` to get a hex string like Flink. (Enable with `CREATE EXTENSION pgcrypto;`.)  [oai_citation:13‡PostgreSQL](https://www.postgresql.org/docs/current/pgcrypto.html?utm_source=chatgpt.com)                                                                                                                                                                                                |
| `SHA224(string)`                                                                                    | Map to `encode(digest(string, 'sha224'), 'hex')` (from `pgcrypto`). Alternatively, cast to `bytea` and use the core `sha224(bytea)` then `encode(...)` to hex.  [oai_citation:14‡Postgres Professional](https://postgrespro.com/docs/postgresql/current/functions-binarystring?utm_source=chatgpt.com)                                                                                                                                                                                                                  |
| `SHA256(string)`                                                                                    | Map to `encode(digest(string, 'sha256'), 'hex')`. (Flink’s return format matches this hex string.)  [oai_citation:15‡Postgres Professional](https://postgrespro.com/docs/postgresql/current/functions-binarystring?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                              |
| `SHA384(string)`                                                                                    | Map to `encode(digest(string, 'sha384'), 'hex')`.  [oai_citation:16‡Postgres Professional](https://postgrespro.com/docs/postgresql/current/functions-binarystring?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                                               |
| `SHA512(string)`                                                                                    | Map to `encode(digest(string, 'sha512'), 'hex')`.  [oai_citation:17‡Postgres Professional](https://postgrespro.com/docs/postgresql/current/functions-binarystring?utm_source=chatgpt.com)                                                                                                                                                                                                                                                                                                                               |
| `SHA2(string, hashLength)`                                                                          | Use a `CASE` on `hashLength` to pick a digest algorithm, e.g.:<br>`CASE hashLength`<br>`  WHEN 224 THEN encode(digest(string,'sha224'),'hex')`<br>`  WHEN 256 THEN encode(digest(string,'sha256'),'hex')`<br>`  WHEN 384 THEN encode(digest(string,'sha384'),'hex')`<br>`  WHEN 512 THEN encode(digest(string,'sha512'),'hex')`<br>`END`.                                                                                                                                                                               |
| `COLLECT([ ALL                                                                                      | DISTINCT ] expression)`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | Flink’s multiset aggregate can be mapped to arrays or JSON. Closest array equivalent:<br>`ARRAY_AGG(expression)` (ALL) or `ARRAY_AGG(DISTINCT expression)` (DISTINCT). To ignore NULLs as Flink does, use `FILTER`: `ARRAY_AGG(expression) FILTER (WHERE expression IS NOT NULL)`.  [oai_citation:19‡PostgreSQL](https://www.postgresql.org/docs/9.5/functions-aggregate.html?utm_source=chatgpt.com) |
| `LISTAGG(expression [, separator])`                                                                 | PostgreSQL equivalent is `STRING_AGG`. Map `LISTAGG(expr, sep)` to `STRING_AGG(expr::text, sep)` (optionally with `ORDER BY` inside the aggregate to control ordering). If `separator` omitted, use `','` to match Flink’s default: `STRING_AGG(expr::text, ',')`.  [oai_citation:20‡PostgreSQL](https://www.postgresql.org/docs/9.5/functions-aggregate.html?utm_source=chatgpt.com)                                                                                                                                   |
| `ARRAY_AGG([ ALL                                                                                    | DISTINCT ] expression [ RESPECT NULLS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | IGNORE NULLS ])` | Base behavior (`ALL` / `DISTINCT` with “respect nulls”) is `ARRAY_AGG([DISTINCT] expression)`. To emulate `IGNORE NULLS`, add a `FILTER` clause: `ARRAY_AGG(expression) FILTER (WHERE expression IS NOT NULL)` or `ARRAY_AGG(DISTINCT expression) FILTER (WHERE expression IS NOT NULL)`.  [oai_citation:21‡PostgreSQL](https://www.postgresql.org/docs/9.5/functions-aggregate.html?utm_source=chatgpt.com) |

# Not Supported Functions

Currently, the following FlinkSQL functions are not supported in Postgres:

| **FlinkSQL Signature**                                                        | **Mapping in PostgreSQL 18+**                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|-------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `PERCENTILE(expr, percentage[, frequency])`                                   | Use `PERCENTILE_CONT` ordered‑set aggregate: `PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr)`. To support `frequency`, expand with `generate_series(1, frequency)`.                                                                                                                                                                                                                                                                                                  |
| `URL_DECODE(string)`                                                          | No core equivalent. Options: <br>• Install an extension like `url_encode` from PGXN and use `url_decode(string)`.  [oai_citation:11‡GitHub](https://github.com/okbob/url_encode?utm_source=chatgpt.com) <br>• Or write a PL/pgSQL helper that: replaces `+` with space, converts `%HH` sequences to bytes with `decode(..., 'hex')`, then `convert_from(..., 'UTF8')`.                                                                                                |
| `URL_ENCODE(string)`                                                          | No core equivalent. Options: <br>• With the `url_encode` extension, use `url_encode(string)` to percent-encode.  [oai_citation:12‡GitHub](https://github.com/okbob/url_encode?utm_source=chatgpt.com) <br>• Or implement your own wrapper that identifies non-unreserved characters and replaces them by `%` + hex using `encode(convert_to(..., 'UTF8'), 'hex')`.                                                                                                    |
| `PARSE_URL(string1, string2[, string3])`                                      | No single built-in. Typically implemented as a custom function using `substring`/`regexp_match`/`split_part`: <br>• Host: `substring(string1 from '://([^/:?#]+)')` <br>• Path: `substring(string1 from '://[^/]+([^?#]*)')` <br>• Query: `split_part(string1, '?', 2)` (then split on `'&'` and `'='` to get key/value, e.g. for `string3`).  [oai_citation:13‡postgresql.org](https://www.postgresql.org/docs/current/functions-string.html?utm_source=chatgpt.com) |
| `JSON_UNQUOTE(string)`                                                        | For a valid JSON string literal, use `string::jsonb #>> '{}'` to get the scalar text value. To mimic “pass through” behavior when it isn’t valid JSON, wrap in a `CASE` checking e.g. a simple regex on the input before casting.  [oai_citation:18‡postgresql.org](https://www.postgresql.org/docs/current/functions-matching.html?utm_source=chatgpt.com)                                                                                                           |
| `CURRENT_ROW_TIMESTAMP()`                                                     | `clock_timestamp()` (returns the actual wall-clock timestamp for each call rather than the transaction start).  [oai_citation:0‡postgresql.org](https://www.postgresql.org/docs/8.2/functions-datetime.html?utm_source=chatgpt.com)                                                                                                                                                                                                                                   |
| `CURRENT_WATERMARK(rowtime)`                                                  | No direct PostgreSQL equivalent; watermarks are a streaming-engine concept. You’d need to model this in application logic (e.g., maintain “max seen event_time minus allowed lateness” in your own tables or code).                                                                                                                                                                                                                                                   |
| `TYPEOF(input)` / `TYPEOF(input, force_serializable)` / `call("TYPEOF", ...)` | Use `pg_typeof(input)::text` to get a textual type name. There is no `force_serializable` flag; for catalog-ready type names you typically combine `pg_typeof` with casts to `regtype` or join to `pg_catalog.pg_type`.  [oai_citation:16‡postgresql.org](https://www.postgresql.org/docs/current/functions-conditional.html?utm_source=chatgpt.com)                                                                                                                  |
| `TRY_CAST(value AS type)` / `ANY.tryCast(TYPE)`                               | PostgreSQL has no built-in `TRY_CAST`. Typical pattern is “validate then cast”, e.g. `CASE WHEN pg_input_is_valid(value, 'integer') THEN value::integer END` for integers, or use a custom PL/pgSQL function with `EXCEPTION` handling.  [oai_citation:15‡Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/203934/postgresql-alternative-to-sql-server-s-try-cast-function?utm_source=chatgpt.com)                                     |
