Contents /
Previous /
Next
Build-In Functions and Operators
Functions
Many functions and operators are available in POSTGRESQL.
<![CDATA[
> SELECT id, name, substr( name, 0, 3) AS "Initals" FROM stud;
id | name | Initals
----+------+---------
1 | fred | fr
3 | tom | to
4 | john | jo
2 | lisa | li
]]>
You can list all functions and their arguments using psql's \df command.
Use psql's \dd command to display comments about any
specific function or group of functions.
Function calls take zero, one, or more arguments and return a single value.
Operators
Operators are symbols (not names) which usually take two arguments
(to the left and right of the operator symbol):
<![CDATA[
> SELECT id, name, (6-semester)
AS "remaining semesters" FROM stud;
id | name | remaining semesters
----+------+---------------------
1 | fred | 4
3 | tom | 5
4 | john | 3
2 | lisa | 4
]]>
Psql's \do command lists all POSTGRESQL operators and their arguments.
The standard arithmetic operators honor the standard precedence rules,
operator precedence (decreasing):
| Operator/Element |
Associativity |
Description |
|---|
|
:: | left |
PostgreSQL-style typecast |
|
[ ] |
left |
array element selection |
| . |
left |
table/column name separator |
| - |
right |
unary minus |
| ^ |
left |
exponentiation |
| * / % |
left |
multiplication, division, modulo |
| + - |
left |
addition, subtraction |
| IS |
|
test for TRUE, FALSE, UNKNOWN, NULL |
| ISNULL |
|
test for NULL |
| NOTNULL |
|
test for NOT NULL |
| (any other) |
left |
all other native and user-defined operators |
| IN |
|
set membership |
| BETWEEN |
|
containment |
| OVERLAPS |
|
time interval overlap |
| LIKE ILIKE |
|
string pattern matching |
| < > |
|
less than, greater than |
| = |
right |
equality, assignment |
| NOT |
right |
logical negation |
| AND |
left |
logical conjunction |
| OR |
left |
logical disjunction |
You can use parentheses to alter this precedence.
Other operators are evaluated in a left-to-right manner,
unless parentheses are present.