This blog post lists all the common functions in PostgreSQL, which can be applied to numbers, strings, Datetime objects, and others.

**Table of content:**Numeric

Correcting functions

Geometric functions

Angle functions

Exponential functions

Aggregate functions

Calculation functions

String

String length

Lower / Upper cases

Concatenate

Trim

Padding

Operation

Substring

Regex

Date – Time – Timestamp

Get current Datetime

Format Datetime

Extraction

Operator

Checking

Other functions

### Numeric

#### Correcting functions

**ABS(x)**: absolute of x.

**CEIL(x) **/ **CEILING(x)**: the smallest integer x.

**FLOOR(x)**: the largest integer x.

**ROUND(x):** the nearest integer to x.

**ROUND(x, d)**: the nearest number with d decimal places.

#### Geometric functions

**SIN(x):** sin(x).

**COS(x): **cos(x).

**TAN(x)**: tan(x).

**COT(x)**: cot(x).

**ACOS(x)**: arc-cos(x). x must be in the range [-1, 1] or else NULL is returned.

**ASIN(x)**: arc-sin(x). x must be in the range [-1, 1] or else NULL is returned.

**ATAN(x):** arc-tan(x).

**ATAN2(y, x)**: the angle between the ray to point (x, y) and the positive x-axis.* (picture from wiki)*

The unit is Radian.

The output is confided in the range (, ).

#### Angle functions

**DEGREES(x)**: convert x from radians to degrees.

**RADIANS(x)**: convert x from degrees to radians.

**PI():** the value of (3.14159…).

#### Exponential functions

**EXP(x)**: .

**LOG(x)**: .

**LOG(b, x)**: .

**POW(x, y)** / **POWER(x, y)**: .

**SQRT(x)**: .

#### Aggregate functions

**GREATEST(x1, x2, x3, x4, …)**: return the biggest input.

**LEAST(x1, x2, x3, x4, …)**: return the smallest input.

#### Calculation functions

**MOD(n, m)**: n mod m

**SIGN(x)**: return -1, 0 or 1 for negative, 0 and positive x, respectively.

### String

#### String length

**BIT_LENGTH(s)**: length of s in bits.

**CHAR_LENGTH(s) / CHARACTER_LENGTH(s)**: length of s in characters.

**LENGTH(s):** length of s in bytes.

#### Lower / Upper cases

**LCASE(s) / LOWER(s)**: s in lowercase.

**UCASE(s) / UPPER(s)**: s in uppercase.

#### Concatenate

**CONCAT_WS(separator, s1, s2, s3, …)**: concatenate s1, s2, s3, etc. with separator.

**CONCAT(s1, s2, s3, …)**: concatenate s1, s2, s3, etc. .

**REPEAT(s, count)**: repeat s `count`

times.

#### Trim

**LTRIM(s)**: s with all spaces on the left removed.

**RTRIM(s)**: s with all spaces on the right removed.

**TRIM(s)**: s will all spaces at both ends removed.

**TRIM([BOTH | LEADING | TRAILING] rem_s FROM s)**: trim all `rem_s`

from s at both ends or on the left or on the right.

#### Padding

**LPAD(s, len, s_pad)**: if the length of s is smaller than `len`

, the function returns s with the string `s_pad`

padded on the left until its length equal to `len`

. If the length of s is bigger than `len`

, return the first `len`

characters of s.

**RPAD(s, len, s_pad)**: if the length of s is smaller than `len`

, the function returns s with the string `s_pad`

padded on the right until its length equal to `len`

. If the length of s is bigger than `len`

, return the **first** `len`

characters of s.

#### Operation

**REPLACE(s, from_s, to_s)**: replace all `from_s`

of s to `to_s`

.

**REVERSE(s)**: reverse s.

#### Substring

**LEFT(s, len)**: the first * len* characters of s.

**MID(s, pos, len) / SUBSTRING(s, pos, len) / SUBSTRING(s FROM pos FOR len)**: from the position `pos`

of s, take `len`

characters.

**POSITION(substr IN s)**: the position of substr in s. (1-indexed.)

**RIGHT(s, len)**: the last `len`

characters of s.

**SUBSTRING(s, pos) / SUBSTRING(s FROM pos)**: substring of s from `pos`

position till the end.

#### Regex

**REGEXP_MATCHES(s, p [, flag])**: return any occurrences of POSIX pattern p in the string s.

**REGEXP_REPLACE(s, p, to_s [, flag])**: return s with POSIX pattern `p`

replaced by `to_s`

.

Some common regexp’s flags are:

- ‘i’ : case-insensitive.
- ‘g’: global search/replace.

### Date – Time – Timestamp

#### Get current Datetime

**CURRENT_DATE**: current date (at the start of the current transaction).

**CURRENT_TIME**: current time (at the start of the current transaction).

**CURRENT_TIMESTAMP / NOW() / TRANSACTION_TIMESTAMP()**: return current Datetime (at the start of the current transaction).

**STATEMENT_TIMESTAMP()**: return current Datetime (at the start of the current statement).

**CLOCK_TIMESTAMP()**: return current Datetime (may change during statement execution).

**LOCALTIME**: (at the start of the current transaction).

**LOCALTIMESTAMP:** (at the start of the current transaction).

**TIMEOFDAY()**: same as CLOCK_TIMESTAMP() but as a string.

#### Format Datetime

**AGE(timestamp_1, timestamp_2)**: the interval between 2 timestamps (timestamp_1 – timestamp_2).

**AGE(timestamp)**: the interval between the current time and the input timestamp.

**DATE_TRUNC(field, timestamp)**: truncate the timestamp to the specified precision. Field.

**JUSTIFY_DAYS(interval)**: adjust the interval so that 30-day is represented by a month.

**JUSTIFY_HOURS(interval)**: adjust the interval so that 24-hour is represented by a day.

**JUSTIFY_INTERVAL(interval)**: apply JUSTIFY_DAYS and JUSTIFY_HOURS.

**TIMESTAMP WITHOUT TIME ZONE the_timestamp AT TIME ZONE zone**: the_timestamp is understood to be in the default (UTC-7) time zone, then be adjusted to the new zone.

**TIMESTAMP WITH TIME ZONE the_timestamp_with_zone AT TIME ZONE zone**: the input timestamp is adjusted to the new zone.

**TIME WITH TIME ZONE the_time_with_zone AT TIME ZONE zone**: the input time is adjusted to the new zone.

**TO_DATE(text, format)**: convert text into a date.

**TO_TIMESTAMP(text, format)**: convert text into a timestamp.

#### Extraction

**DATE_PART(‘field’, timestamp)** / **EXTRACT(field FROM timestamp)**: extract the field from the timestamp. Field.

**DATE_PART(‘field’, interval)** / **EXTRACT(field FROM interval)**: extract the field from the interval. Field.

#### Operator

**date – date**: return an interval.

**date +/- integer**: return a date. the integer is understood to be days.

**date +/- interval**: return a timestamp.

**date + time**: return a timestamp.

**timestamp +/- interval**: return a timestamp.

**timestamp – timestamp**: return an interval.

**interval +/- interval**: return an interval.

**interval * double**: return an interval.

**interval / double**: return an interval.

**time +/- interval:** return a time.

**time – time** return an interval.

#### Checking

**ISFINITE(date / timestamp / interval):** return a boolean indicates if the input is finite.

**Date field:**

microseconds | day | decade |

milliseconds | week | century |

second | month | millennium |

minute | quarter | |

hour | year | epoch |

dow (0-6) | doy | timezone_hour |

isodow (1-7) | timezone | timezone_minute |

### Other functions

**COALESCE(e1, e2, e3, …)**: return the first non-NULL value.

**NULLIF(e1, e2)**: if e1 equals e2, NULL is returned, else e1 is returned.

**CASE ****WHEN condition_1 THEN value_1WHEN condition_2 THEN value_2…[ELSE value_3]END** : branching function.

*References:*