PostgreSQL gilt als das beste frei erhältliche Relationale Datenbanksystem. Es besitzt viele Gemeinsamkeiten mit Oracle, ist aber wesentlich leichter zu administrieren. Hier die wichtigsten Kommandos, um damit an den Start gehen zu können.

PostgreSQL RDBMS installieren (Debian)

# apt-get install postgresql

Zum DB-Admin machen

# su - postgres

Nur von diesem Unix-Account aus kann nach der Installation eine Connection zum DBMS aufgebaut werden.

Liste der existierenden Datenbanken

$ psql -l
[Liste]

Nach der Installation existiert zunächst nur die Datenbank postgres.

Datenbank erzeugen/zerstören

$ createdb DB
...
$ dropdb DB

Liste der existierenden Benutzer

$ psql
postgres=# \du

oder

postgres=# SELECT rolname FROM pg_roles;

Benutzer anlegen/entfernen

$ createuser USER
...
$ dropuser USER

Bei Angabe der Option --superuser erhält der Benutzer Admin-Rechte:

$ createuser --superuser USER

Benutzer sind global für alle Datenbanken einer Installation. Zunächst existiert nur der Benutzer postgres. Soll der User USER von einem anderen Account als dem entsprechenden Unix-Account connecten können, muss ein Passwort vergeben werden. Option -P.

Datenbankzugriff

Ist ein DB-User erzeugt, kann dieser vom gleichnamigen Unix-Account oder per Passwort, falls eins vergeben wurde, von einem anderen Account per psql auf die Datenbank zugreifen.

USER$ psql DB
psql (9.4.3)
Type "help" for help.
...
DB=#

Wurde ein Passwort für User USER vergeben, aber die Anmeldung von einem anderen Accout aus schlägt fehl mit der Meldung

$ psql -U USER DB
psql: FATAL:  Peer authentication failed for user "USER"

dann muss in pg_hba.conf die Authentisierungsmethode für lokale Logins geändert werden von

local     all     all      peer

in

local     all     all      md5

und anschließend der Server neu gestartet werden.

Liste der existierenden Objekte

Liste aller interaktiven psql-Kommandos: \?

Exportieren/Importieren

Session-Einstellungen

Zeitzone

Die Zeitzone kann für eine Session abweichend gesetzt werden:

SET TIME ZONE 'Europe/Berlin';

Die Default-Zeitzone ist in postgresql.conf definiert:

timezone = 'Europe/Berlin'

Datumsformat

Datumsangaben im Format YYYY-MM-DD:

SET datestyle TO iso, ymd;

Aktueller Zeitpunkt:

select localtimestamp(0);

liefert die Zeit entsprechend dem eingestellten Format

2015-06-09 11:44:29

Zeichensatz

Client arbeitet mit ISO-8859-1:

SET client_encoding TO iso88591

Client arbeitet mit UTF-8:

SET client_encoding TO utf8

Siehe: http://www.postgresql.org/docs/9.4/static/multibyte.html

Der clientseitige Zeichensatz kann jederzeit umgeschaltet werden. Wenn Daten mit unterschiedlichem Encoding verarbeitet werden, besteht die Möglichkeit, vor dem Schreiben auf die Datenbank das clientseitige Encoding umzuschalten. Die Konvertierung wird dann vom Server übernommen.

\-Escapes in Stringliteralen verbieten

SET standard_conforming_strings TO on

Server-Zugriff von externen Hosts

Die folgenden Einträge ermöglichen den Zugriff von allen Hosts für alle User und alle Datenbanken.

1. In pg_hba.conf oberhalb der anderen Einträge hinzufügen:

   # TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
   host    all       all   0.0.0.0/0     md5

2. In postgresql.conf eintragen:

   listen_addresses = '*'

Server neu starten

# /etc/init.d/postgresql-X.Y restart

Upgrade auf eine neue Version

Siehe Kapitel "Upgrading" in der Doku.

Links

• PostgreSQL Dokumentation

Gegeben ist eine Tabelle <table> mit einer Kolumne <x>, deren Wert frei manipuliert werden kann, und einer Kolumne <y>, deren Wert funktional von <x> abhängt (also nicht frei manipuliert werden kann). Die Abbildung von <x> auf <y> ist in einer Lookup-Tabelle <lookup_table> definiert, die jedem Wert <x> den entsprechenden Wert <y> zuordnet.

Aufgabe: Der <y>-Wert soll in <table> gespeichert werden und stets konstitent zu <x> sein. Der Wert von <y> soll nicht erst bei Abfrage ermittelt werden. Im Prinzip ist das eine unerwüschte Redundanz, die aber aus praktischen Gründen sinnvoll sein kann.

Die Anforderung lässt sich durch einen BEFORE INSERT OR UPDATE-Trigger erfüllen, der beim Einfügen oder Ändern in <table> den <y>-Wert via <x> in <lookup_table> ermittelt und auf <table>.<y> überträgt.

Es folgt die Lösung für Oracle und PostgreSQL.

Oracle

 1 CREATE OR REPLACE TRIGGER <tigger> BEFORE INSERT OR UPDATE
 2     ON <table> FOR EACH ROW
 3 BEGIN
 4     SELECT
 5         <y>
 6     INTO
 7         :new.<y>
 8     FROM
 9         <lookup_table>
10     WHERE
11         <x> = :new.<x>;
12 END;

PostgreSQL

 1 CREATE FUNCTION <trigger_func>() RETURNS trigger AS $$
 2 BEGIN
 3     SELECT
 4         <y>
 5     INTO STRICT
 6         NEW.<y>
 7     FROM
 8         <lookup_table>
 9     WHERE
10         <x> = NEW.<x>;
11 
12     RETURN NEW;
13 END;
14 $$ LANGUAGE plpgsql;
15 
16 CREATE TRIGGER <trigger> BEFORE INSERT OR UPDATE
17     ON <table> FOR EACH ROW
18     EXECUTE PROCEDURE <trigger_func>();

Der Code ist bei beiden Datenbanksystemen ähnlich, die Unterschiede sind im Wesentlichen:

• Die Prozedur kann bei Oracle als Teil des Trigger definiert werden, was den Code einfach und elegant macht. Bei PostgreSQL ist eine spezielle, vom Trigger getrennte Prozedurdefinition erforderlich.

• Der Bezeichner für die neue Tabellenzeile ist bei Oracle :new, bei PostgreSQL NEW. Bei PostgreSQL muss diese Zeile von der Trigger-Prozedur zurückgeliefert werden. Bei Oracle wird nichts zurückgeliefert.

• PostgreSQL generiert bei INTO STRICT automatisch eine Exception, wenn durch das SELECT keine oder mehr als eine Zeile getroffen wird. Dies ist ein kostenloser Paranoia-Test für die Daten in <lookup_table>.

Startup-Datei anlegen/ändern:

$ vi ~/.psqlrc

Die Kommandos in der Datei führt der Interpreter beim Start aus. Hier können persönliche Einstellungen vorgenommen werden. Z.B. kann man dort den Pager aus- und die Zeitmessung einschalten.

Pager abschalten:

<db>=# \pset pager off
Pager usage is off.

Zeitmessung einschalten:

<db>=# \timing on
Timing is on.

Nützliche interaktive Kommandos:

Liste der Schemata:

<db>=# \dn
...

Liste der Tabellen eines Schemas:

<db>=# \dt <schema>.*
...

Tabelle, View oder Sequenz beschreiben:

<db>=# \d <object>
...

Spezielle SQL-Anweisungen:

Liste der Runtime-Parameter:

<db>=# show all;
...

TIMESTAMP WITHOUT TIME ZONE nach EPOCH

Umwandlung eines TIMESTAMP WITHOUT TIME ZONE in Unix-Epoch (Sekunden seit 1.1.1970 0 Uhr) in einer Anwendung, die mit UTC-Zeiten arbeitet:

sql> SELECT EXTRACT(EPOCH FROM TIMESTAMP
        '1970-01-01 00:00:00' AT TIME ZONE 'UTC') AS t;
 t
---
 0

Entscheidend ist hier der Zusatz "AT TIME ZONE 'UTC'", denn ein TIMESTAMP WITHOUT TIME ZONE wird als Zeit der lokalen Zeitzone interpretiert - nicht etwa UTC! Ohne den Zusatz ist das Resultat um dem Offset der lokalen Zeitzone verschoben - böse Falle. Hier das Ergebnis im Falle von MEZ (+0100):

sql> SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01 00:00:00') AS t;
   t
-------
 -3600

Erläuterungen

• EXTRACT(EPOCH FROM t) extrahiert die Epoch-Sekunden

  vom Zeit-Wert C{t}.

• TIMESTAMP '1970-01-01 00:00:00' ist die Zeitangabe t.

• AT TIME ZONE 'UTC' interpretiert die Zeitangabe t hinsichtlich Zeitzone UTC.

EPOCH nach TIMESTAMP WITHOUT TIMEZONE

Umwandlung von Epoch-Sekunden in einen TIMESTAMP WITHOUT TIMEZONE (die 0 steht für den Epoch-Wert):

sql> gkss=# SELECT TIMESTAMP 'epoch' + 0 * INTERVAL '1 second' AS t;
         t
---------------------
 1970-01-01 00:00:00

Erläuterungen

• TIMESTAMP 'epoch' bzeichnet den Anfang der Zeitrechnung (1.1.1970 0 Uhr).

• 0 * INTERVAL '1 second' ist der Zeitoffset in Sekunden, der hinzuaddiert wird. Er kann auch negativ sein.

• Das Resultat ist ein Timestamp.