PostgreSQL: Functions & Operators

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases

• The usual logical operators of AND, OR, and NOT are available. However, SQL uses a three-valued logic system of TRUE, FALSE and NULL (which represents "unknown"). You should therefore design your system to handle a NULL result.


• The usual comparison operators of >, <, >=, <=, = and <> (or !=, but the parser resolves this to <> anyway) are available.


• The BETWEEN construct allows you to test a value between a range (NOT BETWEEN tests if a value is not within a range)
  

  a BETWEEN x AND y

  is equivalent to
  

  a >= x AND a <= y



• The following extra comparison operators will always resolve to a TRUE or FALSE value:
  

  a IS NULL
  a IS NOT NULL
  a IS TRUE
  a IS NOT TRUE
  a IS FALSE
  a IS NOT FALSE
  a IS UNKNOWN
  a IS NOT UNKNOWN



• Pattern matching can be achieved through LIKE, SIMILAR TO, or POSIX style regular expressions.
  
  LIKE is the simplest pattern matching technique. The _ represents any single character, while a % represents a sequence of characters.
  
  SIMILAR TO is a hybrid approach between LIKE and regular expressions. It still uses the _ and %, but additionally uses symbols borrowed from regular expressions like *, ?, {} and [] (see the documentation for an in-depth explanation).
  
  Regular expression support is also provided, but is an extensive topic that would be too lengthy to include here. A great reference can be found at regular-expressions.info


• PostgreSQL provides a wide array of date & time functions. These include current_date, current_time, and current_timestamp.
  
  Another useful function is age(timestamp), which will subtract the timestamp from the current_date and output a result in years, months and days.


• A CASE expression exists for conditional statements. expression is the SQL expression, value is the matching result of the expression, and result is the value to return
  

  CASE expression
  

  WHEN value THEN result

  [WHEN....]

  [ELSE result]

  END



• The general purpose window functions will only work if invoked via the window function syntax (that is, the OVER clause is required). In addition to in-built aggregate functions, the following general purpose functions will work:
• row_number(): number of the row within the partition
• first_value(field): returns the value of the field from the first row of the window frame
• last_value(field): returns the value of the field from the last row of the window frame
• nth_value(field): returns the value of the field from the nth row of the window frame


• There are many functions to get the current system state, but here are a few of the useful ones:
• version(): Version info
• current_user: Username of the current execution context
• current_database(): Name of the database in use
• current_schema[()]: Name of the current schema
• inet_client_addr(): The address of the remote connection
• inet_client_port(): The port of the remote connection
• pg_conf_load_time(): Configuration loading time


• There also exists many administration functions that allow to configure PostgreSQL at runtime. You can also get other running information from the system such as the physical size of databases, initiate back-ups and recoveries, and signal actions to the server.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• Regular Expressions reference