Functions and operators

Logical and comparison operators

The usual logical operators are still available:

boolean AND boolean -> boolean
boolean OR boolean -> boolean
NOT boolean -> boolean

SQL uses a three-valued logic system with true, false, and null, which represents unknown.

Truth table
`a`	`b`	`a AND b`	`a OR b`	`NOT a`
`TRUE`	`TRUE`	`TRUE`	`TRUE`	`FALSE`
`TRUE`	`FALSE`	`FALSE`	`TRUE`	`FALSE`
`TRUE`	`NULL`	`NULL`	`TRUE`	`FALSE`
`FALSE`	`FALSE`	`FALSE`	`FALSE`	`TRUE`
`FALSE`	`NULL`	`FALSE`	`NULL`	`TRUE`
`NULL`	`NULL`	`NULL`	`NULL`	`NULL`

The operators AND and OR are commutative, that is, you can switch the left and right operands without affecting the result. However, it is not guaranteed that the left operand is evaluated before the right operand.

Comparison operators

The usual comparison operators are available.

Operator	Description
`datatype < datatype -> boolean`	Less than
`datatype > datatype -> boolean`	Greater than
`datatype <= datatype -> boolean`	Less than or equal to
`datatype >= datatype -> boolean`	Greater than or equal to
`datatype = datatype -> boolean`	Equal
`datatype <> datatype -> boolean`	Not equal
`datatype != datatype -> boolean`	Not equal

Mathematical functions and operators

Mathematical operators and provided for many PostgreSQL types.

Operation	Description
`numeric_type + numeric_type`	Addition
`+ numeric_type`	Unary plus (no operation)
`numeric_type - numeric_type`	Subtraction
`- numeric_type`	Negation
`numeric_type * numeric_type`	Multiplication
`numeric_type / numeric_type`	Division (for integral types, division truncates the result towards zero)
`numeric_type % numeric_type`	Modulo (remainder); available for `smallint`, `integer`, `bigint`, and `numeric`
`numeric ^ numeric` `double precision ^ double precision`	Exponentiation
`\|/ double precision`	Square root
`\|\|/ double precision`	Cube root
`@ numeric_type`	Absolute value
`integral_type & integral_type`	Bitwise AND
`integral_type \| integral_type`	Bitwise OR
`integral_type # integral_type`	Bitwise exclusive OR
`~ integral_type`	Bitwise NOT
`integral_type << integer`	Bitwise shift left
`integral_type >> integer`	Bitwise shift right

String functions

Strings in this context include values of the types character, text and character varying.

text || text -> text

Concatenates the two strings.

'Post' || 'greSQL' -> PostgreSQL

text || anynonarray -> text

anynonarray || text -> text

Converts the non-string input to text, then concatenates the two strings. (The non-string input cannot be of an array type, because that would create ambiguity with the array || operators. If you want to concatenate an array’s text equivalent, cast it to text explicitly.)

'Value: ' || 42 -> Value: 42

char_length(text) → integer

character_length(text) → integer

Returns number of characters in the string.

char_length('josé') -> 4

lower(text) → text

Converts the string to all lower case, according to the rules of the database’s locale.

lower('TOM') -> tom

ltrim(string text[, characters text]) → text

Removes the longest string containing only characters in characters (a space by default) from the start of string.

ltrim('zzzytest', 'xyz') -> test

rtrim(string text[, characters text]) → text

Removes the longest string containing only characters in characters (a space by default) from the end of string.

rtrim('testxxzx', 'xyz') -> test

trim([ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text

Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.

trim(both 'xyz' from 'yxTomxx') -> Tom

Pattern matching

There are three separate approaches to pattern matching provided by PostgreSQL: the traditional LIKE operator, the most recent SIMILAR TO operator, and POSIX-style regular expressions.

This section will focus on LIKE operator only.

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

The LIKE expression returns true if the string matches the supplied pattern (as expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa).

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in than case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

Some examples:

'abc' LIKE 'abc'  -- true
'abc' LIKE 'a%'   -- true
'abc' LIKE '_b_'  -- true
'abc' LIKE 'c'    -- false

LIKE patterns matching always covers the entire string. Therefore, if it’s desired to match a sequence anywhere within a string, the pattern must start and end with a percent sign.

To match a literal underscore or percent sing without matching other characters the respective character in pattern must be preceded by the escape character. The default escape character is backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Date/Time functions and operators

Extract

exctract(field FROM source)

The extract function retrieves subfields such as year or hour from date/time value. The source must be a value expression of type timestamp, time, or interval. Expressions of type date are cast to timestamp and can therefore be used as well. field is an identifier or string that selects what field to extract from the source value. The extract function returns value of type numeric. The following are valid field names:

centurty
day
decade
dow
doy
epoch
hour
isodow
isoyear
julian
microseconds
millennium
milliseconds
minute
month
quarter
second
timezone
timezone_hour
timezone_minute
week
year

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

date_part('field', source)

Current date/time

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIME(precision)

Deliver current time value with time zone.

Precision:: An optional parameter, which causes the result to be rounded to that many fractional digits in the second field. Without a precision, the result is given to the full available precision.

CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP(precision)

Deliver current timestamp value with time zone.

Precision:: An optional parameter, which causes the result to be rounded to that many fractional digits in the second field. Without a precision, the result is given to the full available precision.

LOCALTIME()

LOCALTIME(precision)

Deliver current time value without time zone.

Precision:: An optional parameter, which causes the result to be rounded to that many fractional digits in the second field. Without a precision, the result is given to the full available precision.

LOCALTIMESTAMP()

LOCALTIMESTAMP(precision)

Deliver current time value without time zone.

Precision:: An optional parameter, which causes the result to be rounded to that many fractional digits in the second field. Without a precision, the result is given to the full available precision.

Formatting functions

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floatting point, numeric) to formatted strings and for converting from formatted strings to specific data types.

These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

Converts time stamp to string according to the given format.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12

to_char ( interval, text ) → text

Converts interval to string according to the given format.

to_char(interval '15h 2m 12s', 'HH24:MI:SS') → 15:02:12

to_char ( numeric_type, text ) → text

Converts number to string according to the given format; available for integer, bigint, numeric, real, double precision.

to_char(125, '999') → 125
to_char(125.8::real, '999D9') → 125.8
to_char(-125.8, '999D99S') → 125.80-

to_date ( text, text ) → date

Converts string to date according to the given format.

to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05

to_number ( text, text ) → numeric

Converts string to numeric according to the given format.

to_number('12,454.8-', '99G999D9S') → -12454.8

to_timestamp ( text, text ) → timestamp with time zone

Converts string to time stamp according to the given format.

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

Conditional expressions

CASE

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.

COALESCE

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display:

SELECT COALESCE(description, short_description, '(none)') ...

This returns description if it is not null, otherwise short_description if it is not null, otherwise (none).

Like the CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated.

NULLIF

NULLIF(value_1, value_2)

The NULLIF function returns a null value_1 equals value_2; otherwise it returns value_1. This can be used to perform the inverse operation of the COALESCE.

SELECT NULLIF(value, '(none)') ...

In the example above, if value is (none), null is returned, otherwise the value of value is returned.

The two arguments must be of comparable types. To be specific, they are compared exactly as if you had written value_1 = value_2, so there must be a suitable = operator available.

The result has the same type as the first argument – but there is a subtlety. What is actual returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument’s type.

Aggregate functions

Aggregate functions compute a single result from a set of input values. There are lots of these, and different RDBMS implement different aggregation functions. In this section, we will focus on the most wide spread and often used aggregate functions only.

The general schema of using is presented below:

Average value

avg(smallint) → numeric
avg(integer) → numeric
avg(bigint) → numeric
avg(numeric) → numeric
avg(real) → double precision
avg(double precision) → double precision
avg(interval) → interval: Computes the average (arithmetic mean) of all the non-null input values within a group

Important

SQLite implements avg function in a manner to return NULL if and only if all the inputs are nulls. On the other hand PostgreSQL will raise an error in this scenario.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       ceil(avg(amount)) as avg
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year	month	avg
2013	11	7798
2013	12	6709
2014	1	6523
2014	2	6832
2014	3	10218

Count

count(*) → bigint: Computes the number of input rows

count("any") → bigint: Computes the number of input rows in which the input value is not null

SELECT extract(year from date) as year,
       extract(month from date) as month,
       count(*)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year	month	count
2013	11	5
2013	12	7
2014	1	5
2014	2	8
2014	3	5

Maximum value

max(see text) → same as input type: Computes the maximum of the non-null input values. Available for any numeric, string, date/time or enum type.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       max(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year	month	max
2013	11	12383
2013	12	12264
2014	1	8853
2014	2	12919
2014	3	14661

Minimum value

min(see text) → same as input type: Computes the minimum of the non-null inpout values. Available for any numeric, string, date/time or enum type.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       min(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year	month	min
2013	11	3365
2013	12	3564
2014	1	2636
2014	2	3442
2014	3	2654

Sum of values

sum(smallint) → bigint
sum(integer) → bigint
sum(bigint) → numeric
sum(numeric) → numeric
sum(real) → real
sum(double precision) → double precision
sum(interval) → interval
sum(money) → money: Computes the sum of the non-null values

SELECT extract(year from date) as year,
       extract(month from date) as month,
       sum(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year	month	sum
2013	11	38988
2013	12	46958
2014	1	32611
2014	2	54652
2014	3	51087