# Single-row and aggregate functions
<a name="chap-oracle-aurora-pg.sql.aggregate"></a>

Single-row and aggregate functions are essential SQL constructs that perform operations on individual rows or groups of rows, respectively. The following sections compare Oracle and PostgreSQL single-row and aggregate functions.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |  N/A  |  Not all functions are supported by PostgreSQL and may require to create manually.  | 

## Oracle usage
<a name="chap-oracle-aurora-pg.sql.aggregate.ora"></a>

Oracle provides two main categories of built-in SQL functions based on the number of rows used as input and generated as output.
+ Single-row functions (also known as scalar functions) return a single result for each row of the queried table or view. You can use them with a `SELECT` statement in the `WHERE` clause, the `START WITH` clause, the `CONNECT BY` clause, and the `HAVING` clause. The single-row functions are divided into groups according to data types such as `NUMERIC` functions, `CHAR` functions, and `DATETIME` functions.
+ Aggregative Functions (also known as Group functions) are used to summarize a group of values into a single result. Examples include `AVG`, `MIN`, `MAX`, `SUM`, `COUNT`, `LISTAGG`, `FIRST`, and `LAST`.

See the following section for a comparison of Oracle and PostgreSQL single-row functions.

Oracle 19 adds ability to eliminate duplicate items in `LISTAGG` function results with new `DISTINCT` keyword.

Oracle 19 introduces several new bitmap SQL aggregate functions (`BITMAP_BUCKET_NUMBER`, `BITMAP_BIT_POSITION` and `BITMAP_CONSTRUCT_AGG`) that help to speed up `COUNT DISTINCT` operations.

For more information, see [Single-Row Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C) and [Aggregate Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676BAF18-4E63-BD14-25206FEA0848) in *Oracle documentation*.

## PostgreSQL usage
<a name="chap-oracle-aurora-pg.sql.aggregate.pg"></a>

PostgreSQL provides an extensive list of single-row and aggregation functions. Some are similar to their Oracle counterparts (by name and functionality, or under a different name but with similar functionality). Other functions can have identical names to their Oracle counterparts, but exhibit different functionality. In the following tables, the Equivalent column indicates functional equivalency.

 **Numeric functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `ABS`   |  Absolute value of n: `abs (-11.3) → 11.3`.  |   `ABS(n)`   |  Absolute value of n: `abs (-11.3) → 11.3`.  |  Yes  | 
|   `CEIL`   |  Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) → -24`.  |   `CEIL` / `CEILING`   |  Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) → -24`.  |  Yes  | 
|   `FLOOR`   |  Returns the largest integer equal to or less than n: `floor (-43.7) → -44`.  |   `FLOOR`   |  Returns the largest integer equal to or less than n: `floor (-43.7) → -44`.  |  Yes  | 
|   `MOD`   |  Remainder of n2 divided by n1: `mod(10,3) → 1`.  |   `MOD`   |  Remainder of n2 divided by n1: `mod(10,3) → 1`.  |  Yes  | 
|   `ROUND`   |  Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) → 3.5`.  |   `ROUND`   |  Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) → 3.5`.  |  Yes  | 
|   `TRUNC (Number)`   |  Returns n1 truncated to n2 decimal places: `trunc(13.5) → 13`.  |   `TRUNC (Number)`   |  Returns n1 truncated to n2 decimal places: `trunc(13.5) → 13`.  |  Yes  | 

 **Character functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CONCAT`   |  Returns char1 concatenated with char2: `concat('a', 1) → a1`.  |   `CONCAT`   |  Concatenate the text representations of all the arguments: `concat('a', 1) → a1`.  |  Partly  | 
|   `LOWER` / `UPPER`   |  Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |   `LOWER` / `UPPER`   |  Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |  Yes  | 
|   `LPAD` / `RPAD`   |  Returns expr1, left or right padded to length n characters with the sequence of characters in expr2: `LPAD('Log-1',10,'-') → -----Log-1`.  |   `LPAD` / `RPAD`   |  Returns expr1, left or right padded to length n characters with the sequence of characters in expr2: `LPAD('Log-1',10,'-') → -----Log-1`.  |  Yes  | 
|   `REGEXP_REPLACE`   |  Search a string for a regular expression pattern: `regexp_replace('John', '[hn].', '1') → Jo1`.  |   `REGEXP_REPLACE`   |  Replace substring(s) matching a POSIX regular expression: `regexp_replace('John', '[hn].', '1') → Jo1`.  |  Yes  | 
|   `REGEXP_SUBSTR`   |  Extends the functionality of the SUBSTR function by searching a string for a regular expression pattern: `REGEXP_SUBSTR('http://www.aws.-com/products','http://(+\.?){3,4}/?') → http://www.aws.com/`.  |   `REGEXP_MATCHES OR SUBSTRING`   |  Return all captured substrings resulting from matching a POSIX regular expression against the string: `REGEXP_MATCHES ('http://www.aws.com/products', '(http://+./)') → {http://www.aws.com/} OR SUBSTRING ('http://www.aws.-com/products', '(http://+./)') → http://www.aws.-com/`.  |  No  | 
|   `REPLACE`   |  Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |   `REPLACE`   |  Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |  Yes  | 
|   `LTRIM` / `RTRIM`   |  Removes from the left or right end of char all of the characters that appear in set: `ltrim ('zzzyaws', 'xyz') → aws`.  |   `LTRIM` / `RTRIM`   |  Remove the longest string containing only characters from characters (a space by default) from the start of string: `ltrim('zzzyaws', 'xyz') → aws`.  |  Yes  | 
|   `SUBSTR`   |  Return a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.  |   `SUBSTRING`   |  Extract substring: `substring ( 'John Smith', 6 ,1) → S `.  |  No  | 
|   `TRIM`   |  Trim leading or trailing characters (or both) from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.  |   `TRIM`   |  Remove the longest string containing only characters from characters (a space by default) from the start, end, or both ends: `trim (both from 'yxJohnxx', 'xyz') → John`.  |  Partly  | 
|   `ASCII`   |  Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |   `ASCII`   |  Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |  Yes  | 
|   `INSTR`   |  Search string for substring  |  N/A  |  Oracle `INSTR` function can be simulated using PostgreSQL built-in function.  |  No  | 
|   `LENGTH`   |  Return the length of char: `length ('John S.') → 7`.  |   `LENGTH`   |  Return the length of char: `length ('John S.') → 7`.  |  Yes  | 
|   `REGEXP_COUNT`   |  Returns the number of times, a pattern occurs in a source string.  |  N/A  |  You can use the `REGEXP_COUNT` function with Amazon Redshift if necessary.  |  No  | 
|   `REGEXP_INSTR`   |  Search a string position for a regular expression pattern.  |  N/A  |  You can use the `REGEXP_INSTR` function with Amazon Redshift if necessary.  |  No  | 

 **Datetime functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `ADD_MONTHS`   |  Returns the date plus integer months: `add_months( sysdate,1)`   |  N/A  |  PostgreSQL can implement the same functionality using the `<date>+ interval month` statement: `now () + interval '1 month'`.  |  No  | 
|   `CURRENT_DATE`   |  Returns the current date in the session time zone: `select current_date from dual → 2017-01-01 13:01:01`.  |   `CURRENT_DATE`   |  PostgreSQL CURRENT\$1DATE will return date with no time, use the `now()` or the `current_timestamp` function to achieve the same results: `select current_timestamp → 2017-01-01 13:01:01`.  |  Partly  | 
|   `CURRENT_TIMESTAMP`   |  Returns the current date and time in the session time zone: `select current_timestamp from dual; → 2017-01-01 13:01:01`.  |   `CURRENT_TIMESTAMP`   |  Returns the current date and time in the session time zone: `select current_timestamp; → 2017-01-01 13:01:01`.  |  Yes  | 
|   `EXTRACT (date part)`   |  Returns the value of a specified datetime field from a datetime or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |   `EXTRACT (date part)`   |  Returns the value of a specified datetime field from a datetime or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |  Yes  | 
|   `LAST_DAY`   |  Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.  |  N/A  |  You can use the `LAST_DAY` function with Amazon Redshift if necessary or you can create a workaround with PostgreSQL built-in functions.  |  No  | 
|   `BETWEEN`   |  Returns the number of months between dates date1 and date2: `MONTHS_BETWEEN ( sysdate, sysdate-100) → 3.25`.  |  N/A  |  As an alternative solution create a function from PostgreSQL built-in functions to achieve the same functionality. Example for a possible solution without decimal values: `DATE_PART ('month', now()) - DATE_PART('month', now()- interval'100 days') → 3`.  |  No  | 
|   `SYSDATE`   |  Returns the current date and time set for the operating system on which the database server resides: `select sysdate from dual; → 2017-01-01 13:01:01`.  |   `now()`   |  Current date and time including fractional seconds and time zone: `select now (); → 2017-01-01 13:01:01.123456+00`.  |  No  | 
|   `SYSTIMESTAMP`   |  Returns the system date, including fractional seconds and time zone: `select systimestamp from dual; → 2017-01-01 13:01:01.123456 PM+00:00`.  |   `NOW()`   |  Current date and time including fractional seconds and time zone: `select now (); → 2017-01-0113:01:01.123456+00`.  |  No  | 
|   `LOCALTIMESTAMP`   |  Returns the current date and time in the session time zone in a value of data type TIMESTAMP: `select localtimestamp from dual; → 01-JAN-17 10.01.10.123456 PM`.  |   `LOCALTIMESTAMP`   |  Returns the current date and time in the session time zone in a value of data type TIMESTAMP: `select localtimestamp; → 01-JAN-17 10.01.10.123456 PM`.  |  Yes  | 
|   `TO_CHAR(datetime)`   |  Converts a datetime or timestamp to data type to a value of VARCHAR2 data type in the format specified by the date format: `to_char(sys-date, 'DD-MON-YYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`.  |   `TO_CHAR(datetime)`   |  Convert time stamp to string: `TO_CHAR(now(), 'DD-MONYYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`.  |  Yes  | 
|   `TRUNC (date)`   |  Returns a date with the time portion of the day truncated to the unit specified by the format model: `trunc(systimestamp); → 2017-01-01 00:00:00`.  |   `DATE_TRUNC`   |  Truncate to specified precision: `date_trunc('day', now()); → 2017-01-01 00:00:00`.  |  No  | 

 **Encoding and decoding functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `DECODE`   |  Compares expression to each search value one by one using the functionality of an `IF-THEN-ELSE` statement.  |   `DECODE`   |  PostgreSQL Decode function acts differently from Oracle, PostgreSQL decode binary data from textual representation in string and doesn’t have the functionality of an `IF-THEN-ELSE` statement.  |  No  | 
|   `DUMP`   |  Returns a `VARCHAR2` value containing the data type code, length in bytes, and internal representation of expression.  |  N/A  |  N/A  |  No  | 
|   `ORA_HASH`   |  Computes a hash value for a given expression.  |  N/A  |  N/A  |  No  | 

 **Null functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CASE`   |  The `CASE` statement chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |   `CASE`   |  The PostgreSQL `CASE` expression is a generic conditional expression, similar to if/else statements in other programming languages: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |  Yes  | 
|   `COALESCE`   |  Returns the first non-null expr in the expression list: `coalesce (null, 'a', 'b') → a`.  |   `COALESCE`   |  Returns the first of its arguments that isn’t null: `coalesce (null, 'a', 'b') → a`.  |  Yes  | 
|   `NULLIF`   |  Compares expr1 and expr2. If they are equal, the function returns null. If they aren’t equal, the function returns expr1: `NULLIF('a', 'b') → a`.  |   `NULLIF`   |  Returns a null value if value1 equals value2 otherwise it returns value1: `NULLIF ('a', 'b') → a`.  |  Yes  | 
|   `NVL`   |  Replace null (returned as a blank) with a string in the results of a query: `NVL (null, 'a') → a`.  |   `COALESCE`   |  Returns the first of its arguments that isn’t null: `coalesce (null, 'a') → a`.  |  No  | 
|   `NVL2`   |  Determine the value returned by a query based on whether a specified expression is null or not null.  |  N/A  |  Can use the `CASE` statement instead.  |  No  | 

 **Environment and identifier functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `SYS_GUID`   |  Generates and returns a globally unique identifier (RAW value) made up of 16 bytes: `select sys_guid() from dual → 5A280ABA8C76201EE0530-100007FF691`.  |  UUID\$1GENERATE\$1V1()  |  Generates a version 1 UUID: `select uuid_generate_v1() → 90791a6-a359-11e7-a61c-12803bf1597a`.  |  No  | 
|   `UID`   |  Returns an integer that uniquely identifies the session user (the user who logged on): `select uid from dual → 84`   |  N/A  |  Consider using the PostgreSQL current\$1user function along with other PostgreSQL built-in function to generate a UID.  |  No  | 
|   `USER`   |  Returns the name of the session user: `select user from dual`.  |   `USER` / `SESSION_USER` / `CURRENT_USER` / `CURRENT_SCHEMA()`   |  User name or schema of current run context: `select user;` or `select current_schema();`   |  No  | 
|   `USERENV`   |  Returns information about the current session using parameters: `SELECT USERENV ('LANGUAGE') "Language" FROM DUAL`   |  N/A  |  For a list of all system functions, see the [PostgreSQL documentation](https://www.postgresql.org/docs/13/functions-info.html).  |  No  | 

 **Conversion functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CAST`   |  Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as int) + 1 → 11`.  |   `CAST`   |  Converting one data type into another: `cast ( '10' as int) + 1 → 11`.  |  Yes  | 
|   `CONVERT`   |  Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual`   |  N/A  |  N/A  |  No  | 
|   `TO_CHAR (string / numeric)`   |  Converts `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB` data to the database character set: `select to_char ('01234') from dual → 01234`.  |   `TO_CHAR`   |  Converts the first argument to the second argument: `select to_char (01234, '00000') → 01234`.  |  No  | 
|   `TO_DATE`   |  Converts char of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `DATE` data type: `to_date('01Jan2017','DDMonYYYY') → 01-JAN-17`.  |   `TO_DATE`   |  Convert string to date: `to_date('01Jan2017', 'DDMonYYYY') → 2017-01-01`.  |  Partly  | 
|   `TO_NUMBER`   |  Converts expr to a value of `NUMBER` data type: `to_number('01234') → 1234 or to_number('01234', '99999') → 1234`.  |   `TO_NUMBER`   |  Convert string to numeric: `to_number('01234', '99999') → 1234`.  |  Partly  | 

 **Aggregate functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `AVG`   |  Returns average value of expression: `select avg(salary) from employees`.  |   `AVG`   |  Average (arithmetic mean) of all input values: `select avg(salary) from employees`.  |  Yes  | 
|   `COUNT`   |  Returns the number of rows returned by the query: `select count(*) from employees`.  |   `COUNT`   |  The number of input rows: `select count(*) from employees`.  |  Yes  | 
|   `LISTAGG`   |  Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select listagg(firstname,' ,') within group (order by customerid) from customer`.  |   `STRING_AGG`   |  Input values concatenated into a string, separated by delimiter: `select string_agg(firstname, ' ,') from customer order by 1;`.  |  No  | 
|   `MAX`   |  Returns the maximum value of expression: `select max(salary) from employees`.  |   `MAX`   |  Returns maximum value of expression: `select max(salary) from employees`.  |  Yes  | 
|   `MIN`   |  Returns the minimum value of expression: `select min(salary) from employees`.  |   `MIN`   |  Returns minimum value of expression: `select min(salary) from employees`.  |  Yes  | 
|   `SUM`   |  Returns the sum of values of expression: `select sum(salary) from employees`.  |   `SUM`   |  Returns the sum of values of expression: `select sum(salary) from employees`.  |  Yes  | 

 **Top-N query Oracle 12c** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `FETCH`   |  Retrieves rows of data from the result set of a multi-row query: `select * from customer fetch first 10 rows only`.  |   `FETCH` or `LIMIT`   |  Retrieve just a portion of the rows that are generated by the rest of the query: `select * from customer fetch first 10 rows only`.  |  Yes  | 

 `REGEXP_MATCH` is a new pattern matching function that was introduced in PostgreSQL 10.

```
SELECT REGEXP_MATCH('foobarbequebaz','bar.*que');
regexp_match
-------------
{barbeque}
```

For more information, see [Functions and Operators](https://www.postgresql.org/docs/13/functions.html), [Mathematical Functions and Operators](https://www.postgresql.org/docs/13/functions-math.html), [String Functions and Operators](https://www.postgresql.org/docs/13/functions-string.html), and [uuid-ossp Functions](https://www.postgresql.org/docs/13/uuid-ossp.html) in the *PostgreSQL documentation*.