PostgreSQL Syntax

(Unique) Index erstellen

CREATE INDEX title_idx ON films (title);
CREATE UNIQUE INDEX title_idx ON films (title, year);
ALTER TABLE foo ADD PRIMARY KEY (x, y);

Foreign Key erstellen

CREATE TABLE products (
    product_no integer PRIMARY KEY
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Char in Integer umwandeln

Es gibt verrschiedene Möglichkeiten:

  • Automatisches CAST
  • CAST mit :: Schreibweise
  • CAST mit der CAST-Funktion

select 1+'1' as a, 1+('1'::integer) as b, 1+cast('1' as integer) as c;

 a | b | c 
---+---+---
 2 | 2 | 2
(1 Zeile)

Datetime / Timestamp in Character formatieren

to_char(datetime, format)

wobei mit format die Formatierung definiert wird: 

Muster  Beschreibung
HH24    Stunde (00-23)
MI      Minute
SS      Sekunde
YYYY    Jahr
MM      Monat (01-12)
DD      Tag (01-31)

Beispiel: 

to_char(date, 'YYYY-MM-DD HH24:MI:SS')

liefert ein Datum wie 2010-06-14 17:21:32.

Weitere Muster hier: http://www.postgresql.org/docs/9.4/static/functions-formatting.html

Zeitabstand zwischen Timestamps / Intervall in Sekunden

Für date und timestamp Werte wird die Anzahl der Sekunden seit 1970-01-01 00:00:00 UTC ausgegeben: 

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');

Result: 982384720.12

Für interval Werte wird die Anzahl der Sekunden des Intervalls ausgegeben: 

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');

Result: 442800

SELECT EXTRACT(EPOCH FROM date2 - date1);

Result: 4711

null-safe equal-operator

Ist einer der Operanden bezüglich eines Vergleichsoperators NULL, so ist das Ergebnis der Vergleichsoperation typischerweise ebenfalls NULL: 

select 1=NULL as "1=NULL", NULL=NULL as "NULL=NULL";

1=NULL | NULL=NULL
----------+-----------
NULL     | NULL
(1 Zeile)

Möchte man zwei Werte vergleichen und auch true ausgeben, wenn beide OperandenNULL sind, dann geht das zunächst so: 

select a=b or (a is null and b is null);

Setzt man NULL für a und b ein, ist das Ergebnis true: 

select NULL=NULL or (NULL is null and NULL is null) as test;

 test 
----------
 t
(1 Zeile)

Das ist umständlich. MySQL bildet dieses Verhalten mit dem null-safe equal-Operator <=> ab. Statt = schreibt man in MySQL einfach <=> und bekommt auch dann true, wenn beide Werte NULL sind. In postgreSQL kann man sich diesen Operator selbst erstellen: 

drop operator if exists <=> (anyelement, anyelement);
drop function if exists nullsafeequal (anyelement, anyelement);
create function nullsafeequal (anyelement, anyelement)
RETURNS BOOLEAN as '
select 
CASE WHEN ($1 is null and $2 is null) THEN TRUE
ELSE
    CASE WHEN ($1 is null or $2 is null) THEN FALSE
    ELSE ($1=$2)
    END
END
'
LANGUAGE SQL;
drop operator if exists <=> (anyelement, anyelement);
create operator <=> (procedure = nullsafeequal, 
leftarg=anyelement, rightarg=anyelement);

Damit hat man auch auf der postgreSQL einen null-safe equal-Oparator <=>: 

SELECT
    1<=>1 as "1<=>1",
    1<=>0 as "1<=>0",
    1<=>null as "1<=>null",
    null<=>1 as "null<=>1",
    null::integer<=>null as "null<=>null"
;

 1<=>1 | 1<=>0 | 1<=>null | null<=>1 | null<=>null 
-------+-------+----------+----------+-------------
 t     | f     | f        | f        | t
(1 Zeile)

Zu beachten ist, dass mindestens bei einem der beiden Parameter von <=> ein Datentyp bekannt sein muss (notfalls casten, s.o.). Sonst kommt ein Fehler: 

ERROR:  could not determine polymorphic type because input has type "unknown"

Multi-Table Update mit Aliasen

UPDATE table1 as t1
SET col1=t2.col1
FROM table2 as t2
WHERE t1.id=t2.id;

Wichtig ist, dass

  • table1 nicht nochmal in FROM auftaucht (außer man möchte einen self-join machen)
  • man den Alias t1 bei SET nicht verwendet (SET t1.col1=... wirft einen Fehler)

Das as ist optional und kann auch weggelassen werden.

Escape-Zeichen in Strings verwenden

Um z.B. ein TAB-Zeichen darzustellen, kann eine Escape-Sequenz \t verwendet werden. In PostgreSQL wird dieses jedoch nur ausgewertet, wenn ein E vorgestellt wird. Ansonsten wird ein Fehler geworfen. Beispiel: 

SELECT E'x\tx' as test;

   test    
-----------
 x       x
(1 Zeile)

NULLs in Integer-Attribut einfügen

Man kann NULL nicht einfach in ein Integer-Attribut einfügen. Stattdessen muss man zuvor ein CAST machen: 

CREATE testtabe (a integer);
INSERT into testtable values (CAST(NULL AS int));
INSERT into testtable SELECT (CAST(NULL AS int) from abctable);