Comparison

Comparison predicates

datatype BETWEEN datatype AND datatype

Test if a value is within the range endpoints (inclusive)

2 BETWEEN 1 AND 3 -- true
2 BETWEEN 3 AND 1 -- false

datatype NOT BETWEEN datatype AND datatype

Not between (the negation of BETWEEN).

2 NOT BETWEEN 1 AND 3 -- false

datatype BETWEEN SYMMETRIC datatype AND datatype

Between, after sorting the two endpoint values.

2 BETWEEN SYMMETRIC 3 AND 1 -- true

datatype NOT BETWEEN SYMMETRIC datatype AND datatype

Not between, after sorting the two endpoint values.

2 NOT BETWEEN SYMMETRIC 3 AND 1 -- false

datatype IS DISTINCT FROM datatype

Not equal, treating null as a comparable value.

1 IS DISTINCT FROM NULL -- true (rather than NULL)
NULL IS DISTINCT FROM NULL -- false (rather than NULL)

datatype IS NOT DISTINCT FROM datatype

Equal, treating null as a comparable value.

1 IS NOT DISTINCT FROM NULL -- false (rather than NULL)
NULL IS NOT DISTINCT FROM NULL -- true (rather than NULL)

datatype IS NULL

Test whether value is null.

1.5 IS NULL -- false

datatype IS NOT NULL

Test whether value is not null.

'null' IS NOT NULL -- true

datatype ISNULL: Test whether value is null (nonstandard syntax).

datatype NOTNULL: Test whether value is not null (nonstandard syntax).

boolean IS TRUE

Test whether boolean expression yields true.

true IS TRUE -- true
NULL::boolean IS TRUE -- false (rather than NULL)

boolean IS NOT TRUE

Test whether boolean expression yields false or unknown.

true IS NOT TRUE -- false
NULL::boolean IS NOT TRUE -- true (rather than NULL)

boolean IS FALSE

Test whether boolean expression yields false.

true IS FALSE -- false
NULL::boolean IS FALSE -- false (rather than NULL)

boolean IS NOT FALSE

Test whether boolean expression yields true or unknown.

true IS NOT FALSE -- true
NULL::boolean IS NOT FALSE -- true (rather than NULL)

boolean IS UNKNOWN

Test whether boolean expression yields unknown.

true IS UNKNOWN -- false
NULL::boolean IS UNKNOWN -- true (rather than NULL)

boolean IS NOT UNKNOWN

Test whether boolean expression yields true or false.

true IS NOT UNKNOWN -- true
NULL::boolean IS NOT UNKNOWN -- false (rather than NULL)

Comparison functions

num_nonnulls(VARIADIC "any"): Returns the number of non-null arguments

SELECT num_nonnulls(1, NULL, 2) -- return 2

num_nulls(VARIADIC "any"): Returns the number of null arguments

SELECT num_nulls(1, NULL, 2) -- return 1

Mathematical functions and operators

numeric_type + numeric_type

Addition

SELECT 2 + 3 -- returns 5

+ numeric_type

Unary plus (no operation)

SELECT + 3.5 -- returns 3.5

numeric_type - numeric_type

Subtraction

SELECT 2 - 3 -- returns -1

- numeric_type

Negation

SELECT - (-4) -- returns 4

numeric_type * numeric_type

Multiplication

SELECT 2 * 3 -- returns 6

numeric_type / numeric_type

Division (for integral types, division truncates the result towards zero)

SELECT 5.0 / 2 -- returns 2.5000000000000000

SELECT 5 / 2 -- returns 2

SELECT (-5) / 2 -- returns -2

numeric_type % numeric_type

Modulo (remainder); available for smallint, integer, bigint, and numeric

SELECT 5 % 4 -- returns 1

numeric ^ numeric

double precision ^ double precision

Exponentiation

SELECT 2 ^ 3 -- returns 8

Unlike typical mathematical practice, multiple uses of ^ will associate left to right by default:

SELECT 2 ^ 3 ^ 3 -- returns 512
SELECT 2 ^ (3 ^ 3) -- returns 134217728

|/ double precision

Square root

SELECT |/ 25.0 -- returns 5

||/ double precision

Cube root

SELECT ||/ 64.0 -- returns 4

@ numeric_type

Absolute value

SELECT @ -5.0 -- returns 5.0

integral_type & integral_type

Bitwise AND

SELECT 91 & 15 -- returns 11

integral_type | integral_type

Bitwise OR

SELECT 32 | 3 -- returns 35

integral_type # integral_type

Bitwise exclusive OR

SELECT 17 # 5 -- returns 20

~ integral_type

Bitwise NOT

SELECT ~1 -- returns -2

integral_type << integer

Bitwise shift left

SELECT 1 << 4 -- returns 16

integral_type >> integer

Bitwise shift right

SELECT 8 >> 2 -- returns 2