17th September 2020: PostgreSQL 13 RC 1 Released!

Unsupported versions:
7.0

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

SQL92 allows *expressions* to transform data in tables.
Expressions may contain operators (see Operators for more details) and functions
(Functions has more
information).

An expression is one of the following:

( a_expr ) |

constant |

attribute |

a_expr binary_operator a_expr |

a_expr right_unary_operator |

left_unary_operator
a_expr |

parameter |

functional expression |

aggregate expression |

We have already discussed constants and attributes. The three kinds of operator expressions indicate respectively binary (infix), right-unary (suffix) and left-unary (prefix) operators. The following sections discuss the remaining options.

A *parameter* is used to indicate a
parameter in a SQL function. Typically this is used in SQL function
definition statement. The form of a parameter is:

$number

For example, consider the definition of a function, `dept`, as

CREATE FUNCTION dept (name) RETURNS dept AS 'select * from dept where name=$1' LANGUAGE 'sql';

A *functional expression* is the name of
a legal SQL function, followed by its argument list enclosed in
parentheses:

function(a_expr[,a_expr... ] )

For example, the following computes the square root of an employee salary:

sqrt(emp.salary)

An *aggregate expression* represents the
application of an aggregate function across the rows selected by a
query. An aggregate function reduces multiple inputs to a single
output value, such as the sum or average of the inputs. The syntax
of an aggregate expression is one of the following:

aggregate_name (expression) |

aggregate_name (ALL
expression) |

aggregate_name (DISTINCT
expression) |

aggregate_name ( * ) |

The first form of aggregate expression invokes the aggregate across all input rows for which the given expression yields a non-null value. The second form is the same as the first, since ALL is the default. The third form invokes the aggregate for all distinct non-null values of the expression found in the input rows. The last form invokes the aggregate once for each input row regardless of null or non-null values; since no particular input value is specified, it is generally only useful for the count() aggregate.

For example, count(*) yields the total number of input rows; count(f1) yields the number of input rows in which f1 is non-null; count(distinct f1) yields the number of distinct non-null values of f1.

A *target list* is a parenthesized,
comma-separated list of one or more elements, each of which must be
of the form:

wherea_expr[ ASresult_attname]

A *qualification* consists of any number
of clauses connected by the logical operators:

NOT |

AND |

OR |

The *from list* is a comma-separated list
of *from expressions*. Each "from
expression" is of the form:

[whereclass_reference]instance_variable{, [class_ref]instance_variable... }

The "from expression" defines one or more instance variables to range over the class indicated inclass_name[ * ]