Functions and Operators

Chapter Functions and Operators

Table of Contents

Logical Operators

Comparison Functions and Operators

Mathematical Functions and Operators

String Functions and Operators

 format

Binary String Functions and Operators

Bit String Functions and Operators

Pattern Matching

 LIKE

 SIMILAR TO Regular Expressions

 POSIX Regular Expressions

Data Type Formatting Functions

Date/Time Functions and Operators

 EXTRACT, date_part

 date_trunc

 date_bin

 AT TIME ZONE

 Current Date/Time

 Delaying Execution

Enum Support Functions

Network Address Functions and Operators

UUID Functions

JSON Functions and Operators

 Processing and Creating JSON Data

 The SQL/JSON Path Language

Sequence Manipulation Functions

Conditional Expressions

 CASE

 COALESCE

 NULLIF

 GREATEST and LEAST

Array Functions and Operators

Aggregate Functions

Window Functions

Subquery Expressions

 EXISTS

 IN

 NOT IN

 ANY/SOME

 ALL

 Single-Row Comparison

Row and Array Comparisons

 IN

 NOT IN

 ANY/SOME (array)

 ALL (array)

 Row Constructor Comparison

 Composite Type Comparison

Set Returning Functions

System Information Functions and Operators

System Administration Functions

 Configuration Settings Functions

 Server Signaling Functions

Tacnode provides a large number of functions and operators for the built-in data types. This chapter describes most of them, although additional special-purpose functions appear in relevant sections of the manual. The psql commands \df and \do can be used to list all available functions and operators, respectively.

The notation used throughout this chapter to describe the argument and result data types of a function or operator is like this:

repeat ( text, integer ) → text

which says that the function repeat takes one text and one integer argument and returns a result of type text. The right arrow is also used to indicate the result of an example, thus:

repeat('Pg', 4) → PgPgPgPg

If you are concerned about portability then note that most of the functions and operators described in this chapter, with the exception of the most trivial arithmetic and comparison operators and some explicitly marked functions, are not specified by the SQL standard. Some of this extended functionality is present in other SQL database management systems, and in many cases this functionality is compatible and consistent between the various implementations.