PostgreSQL Documentation
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.
Inhaltsverzeichnis |
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:
WITH monate AS ( SELECT count, ( '{"Jan","Feb","Mär","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez"}' ::varchar[] )[count] AS kurzbez FROM ( SELECT generate_series(1,12) AS count ) AS monatszaehler ), SELECT ....
VI-I UPDATE
Synopsis:
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 ] [, ...] ]