Functions and expressions are used in [[Postgres]] to accomplish various tasks. They can be found in both [[data definition language|DDL]] and [[data manipulation language|DML]] statements, but are most often found in [[SELECT]] statements. ## aggregation Aggregation functions are used in a [[SELECT]] statement that contains a [[GROUP BY]] clause. - `COUNT()` - `SUM()` - `AVG()` - `MIN()` - `MAX()` ## distinct - `DISTINCT`: Use in a `SELECT` statement to return only unique values ## date and time functions See [[working with dates]] for more help on date and time operations. - `NOW()` - `CURRENT_DATE()` - [[DATE_PART]]() - [[TO_CHAR]]() ## conversion and casting - [[CAST]]() -> `::` is PostgreSQL shorthand - [[TO_CHAR]]() - [[EXTRACT]]() ## conditional expressions Conditional expressions can be used for basic logic control flow. - [[CASE]] - `COALESCE()` : takes any number of arguments and returns the first value that is not null. - `NULLIF()` : nullify a value ## math functions Use math functions with numerical data types. - `ROUND()` - `FLOOR()` - `ABS()` - `POWER()` - `AVG()` ## string functions Use string functions with strings. - `CONCAT()` - `SUBSTRING()` - `REPLACE()` - `UPPER()` - `LOWER()` - `LENGTH()` - `TRIM()`