PostgreSQL Documentation

Aus FreiBier
Wechseln zu: Navigation, Suche

On this page I collect my most needed parts of the PostgreSQL 8.4 Documentation. I hope the information here will become so dense that I do no more need to use google for every single query I write... :-) For the content here I lean on the order and chapter numbering of the original documentation.

See also SQL Queries for examples.


5.7. Schemas

You can set the search_path. You can also look at the SET command (8.4.11). An example is

 SET search_path TO "$user",public,adempiere;

7.2 Table Expressions

Tells something about the different kinds of join expressions (whereas 2.6 Joins between tables tells almost nothing).

9.4 String Functions and Operators

  • String concatenation
string || string
  • String concatenation with one non-string input
string || non-string or non-string || string
  • substring
substring(string [from int] [for int])
Example: substring('Thomas' from 2 for 3) = 'hom'
  • Split string on delimiter and return the given field (counting from one)
split_part(string text, delimiter text, field int)

9.7 pattern matching

LIKE uses the escape characters '%' and '_'. ILIKE is case insensitive.

[NOT] SIMILAR TO ... uses SQL's regular expressions. These use '%' and '_' like LIKE and '|', '*', brackets '(...)' and character classes '[...]' as in regexes. It always matches the whole string (when not used with '%...%').

Regular Expressions: ~ is a regular expression (~* is case insensitive; !~ and !~* are negations) They use POSIX regualr expressions. I may match a part of the string (when not used with ^...$).

9.19 Window Functions

In former (mySQL-) times I often wondered why one can only get information from one record at a time. Window functions go above this restriction. The allow to calculate the difference to the last record or to see the number of the actual record in the list.

If you want to calculate a sum of all records (a "progressive balance" or a "Saldo") you have to use WITH RECURSIVE.

9.22 set returning functions

 generate_series(start, stop)

produces a list of records with a value. In the most common and simplest case you can use integers. If you nee you can also give a step width and use the number type or even timestamps.

9.17 array functions

Also see 8.14 Array Data Type. See this funny piece of code to see how I used arrays and set returning functions to get Month Strings:

monate AS (
    )[count] AS kurzbez
  FROM (
    SELECT generate_series(1,12) AS count
  ) AS monatszaehler



UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM fromlist ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Meine Werkzeuge