Zugriffsberechtigungen werden in pg_hba.conf im data-Verzeichnis gesetzt. Bei Debian ist die Datei ein Link auf eine mit gleichem Namen in /etc/postgresql/. Bei einer "make install" Installation von den Sourcen ist das /usr/local/pgsql/data
Now installing the PostgreSQL database files in /var/lib/postgres/data
su - postgres -c cd /var/lib/postgres; . ./.profile; LANG=C initdb --encoding LATIN1 --pgdata /var/lib/postgres/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Fixing permissions on existing directory /var/lib/postgres/data... ok
creating directory /var/lib/postgres/data/base... ok
creating directory /var/lib/postgres/data/global... ok
creating directory /var/lib/postgres/data/pg_xlog... ok
creating directory /var/lib/postgres/data/pg_clog... ok
creating template1 database in /var/lib/postgres/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
creating system views... ok
loading pg_description... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. You can now start the database server using:
/usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data
or
/usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgres/data -l logfile start
PostgreSQL database now installed.
Use /usr/bin/createdb to create a specific database and
/usr/bin/createuser to enable other users to connect to a
PostgreSQL database.
In the first instance, these commands must be run by the
user 'postgres'.
Restarting PostgreSQL database: postmaster
No /usr/lib/postgresql/bin/postmaster found running; none killed.
Starting PostgreSQL postmaster.
postmaster successfully started
.
Enabling the PL procedural language in all PostgreSQL databases...
plpgsql added to template1
ls -l dumpall/7.2/
psql template1 < /root/complete.dump
pg_dump -f webcall-pg.dump -C -U postgres webcall
less webcall-pg.dump
/usr/lib/postgresql/bin/postgresql-dump -t /tmp/postgres_050613.dump less /tmp/postgres_050613.dump man postgresql-dump /usr/lib/postgresql/bin/postgresql-dump -n -t /tmp/postgres_050613b.dump -d /usr/lib/postgresql/bin/postgresql-dump -v -i -l -t /tmp/postgres_050613b.dump -d less /tmp/reload.trace pg_ctl reload /usr/lib/postgresql/bin/pg_ctl reload
Wenn man nicht gerade eine Unmenge an Daten in der Datenbank hat, ist das default ASCII bzw. sql Format das richtige, zumal die Daten nicht als insert Befehle sondern als copy Befehle in der Datei abgelegt werden.
pg_dump -a -t large_a -t large_c -t other_large db_name > data_content.sql pg_dump -s db_name > schema_objects_only.sql
pg_dumpall, das den ganzen Cluster sichert, wird man typischerweise als Superuser, also mit der Option -U postgres ausführen.
User Defined Functions werden als Teile der einzelnen Datenbank bzw. sogar des jeweiligen Schemas in der Datenbank angesehen und deshalb mit pg_dump -s gesichert und nicht etwa mit pg_dumpall -g. Gleiches gilt für Trigger, wobei man das in dem Fall auch ohnehin erwartet hätte, da Trigger enger mit Tabellen zusammenhängen als Funktionen.
Im Gegensatz zu MySql, lässt sich nicht mal schnell auf dem Dateisystem nachsehen, welche Datenbanken und Tabellen es gibt. Die Zuordnung zwischen Datenbanken/Tabellen und Dateien ist bei Postgres nämlich nicht linear. 2 Datenbanken gibt es aber immer:
template0 und template1
Aber einloggen kann man sich nur auf 'template1', 0 lässt keine Connections zu. Der root user heißt 'postgres' und nicht 'root', also muss man ein
su postgres
als root ausführen und dann 'psql template1' aufrufen. Der Login sollte immer klappen, solange in pg_hba.conf 'trust' für local users gesetzt ist.
| Name | Type | Owner |
|---|---|---|
| pg_database | table | postgres |
| pg_description | table | postgres |
| pg_language | table | postgres |
| pg_rules | view | postgres |
| pg_shadow | table | postgres |
| pg_tables | view | postgres |
| pg_trigger | table | postgres |
| pg_type | table | postgres |
| pg_user | view | postgres |
| pg_views | view | postgres |
Wichtigste \-Befehle: \l List databases
\c dbname connect to other database
\? help on other \-commands
\dt show tables
\dS list system tables
Schick wäre es wenn man den Pager abschalten könnte, denn sonst muss man sich immer noch mal aus dem Pager ausklinken, sobald mal mehr als 25 Zeilen ausgegeben werden. Und im Emacs interactive sql-mode ist das nichts. \a \t \x die alle als Kandidaten in Frage kämen, helfen hier nicht. Die Option ist etwas versteckt und wird mit dem \-Befehl fürs generelle Ausgabe formatieren gesetzt:
\pset pager tee oder \pset pager
sollten beide ein echo in das Terminal ausgeben, das der Pager off ist. Im Emacs ist es wahrscheinlich am sinnvollsten das client Programm psql mit der Kommandozeilen-Option -P pager=tee zu starten. Glücklicherweise erledigt das Emacs schon von selbst und auch ein bisschen weniger hackerhaft: er setzt nämlich -P pager=off. Im Gegensatz zu \pset benötigt die Option -P aber ein Gleichheitszeichen zwischen Variable 'pager' und value 'tee'.
Bedeutung der Kürzel für die Zugriffsrechte, die mit \z ausgegeben werden können:
\z tablename oder \z PATTERN
a --> INSERT (append) r --> SELECT (read) w --> UPDATE (write) d --> DELETE R --> RULE x --> REFERENCES t --> TRIGGER
Im Gegensatz zu Oracle kennt Postgres einen Unterschied zwischen NULL und dem leeren String. Damit der auch im Terminal zu sehen ist, hilft \pset
\pset null null
Wer sich wundert, wieso das psql Terminal und die anderen Client-Applikationen, die mit dem Postgres Datenbank-Server installiert werden deutsch sprechen, ohne dass man das eingestellt hat, sollte es mal vor dem Aufruf von psql in der Console mit:
set LANG=C
versuchen. Das ist einfacher als eine englische Windows Version zu installieren.
Mit postgres lassen sich relativ problemlos Tabellen mit session scope anlegen, die nur für diese Session sichtbar sind. Sie haben ziemlich viel mit lokalen Variablen gemeinsam, denn wenn es eine reale Tabelle und eine temporary Tabelle mit gleichem Namen gibt, dann wird in eben dieser Session immer die temporäre Tabelle genutzt.
CREATE TEMPORARY TABLE tmp_table ( id INTEGER, content VARCHAR, CONSTRAINT tmp_table_pk PRIMARY KEY (id) );
Indices können also auch auf temporäre Tabellen gesetzt werden.
SET DATESTYLE TO ISO;
(außerdem gibt es noch SQL,Postgres,...)2001-06-13 12:24:05+02 (+02 ist der offset zu UTC)
Beim insert in eine TIMESTAMP oder DATE Spalte kann auch 'now' als string angegeben werden. Es gibt auch 'current', aber das wird man in den seltesten Fällen brauchen, den es evaluiert die Zeit nicht nur einmal beim insert, sondern immer wieder bei jedem Zugriff (select).
select date('1980-06-25') + interval('21 years 8 days');
2001-07-03 00:00:00
Postgres kennt eine Erweiterung ILIKE die einen case-independent Vergleich durchführt, also als Kurzform für:
upper(x) like upper(y)
dient.
Auch bei Postgres gilt wie bei Oracle, dass NULL Column Values beim Aggregieren ignoriert werden. Die Ausnahme ist wie üblich COUNT().
Alle Zeilen, die ein Element 'eins' in der Array-Spalte array_col haben:
where ANY(array_col) = 'eins'
Wenn's um mehr Elemente geht, die alle in der Array-Spalte enthalten sein sollen, gibt es den speziellen Operator @>
where array_col @> ('eins','drei')
Updates können spezielle Funktionen nutzen, die array Spalten modifizieren:
array_append(array_col, 'vier') array_prepend('null',array_col)
string_to_array ist das Analogon zur split Funktion, die man aus vielen
Skript-Programmiersprachen kennt. Allerdings muss man sich wohl anfangs
noch an die ungewohnte, da umgekehrte, Parameter-Reihenfolge gewöhnen. Und wenn das so
entstandene Array auch noch an unnest weiter gereicht wird, wird jedes
Array Element in eine separate Zeile (4 Zeilen) entpackt (unnest wird ab
PostgreSQL 8.4 unterstützt):
SELECT unnest(string_to_array('192.168.6.125','.')) as x;
PostgreSQL ermöglicht es seit Version 8.3 XML Strings direkt in einer Datenbank-Tabelle zu speichern und zwar mit einem Datentyp XML:
create table test_xml ( _id Integer, xmldata xml );
==> select xmlconcat(xmldata) from test_xml;
xmlconcat
------------------------------------------------
<root><subelem attr="imp">321</subelem></root>
<book issue="1883">Krieg und Frieden</book>
(2 rows)
| xmlconcat ist für XML das, was der Verkettungsoperator | für das normale |
|---|
SQL ist. In obiger Konstellation tut er aber nicht viel, da es nichts zu verketten gibt. Es gibt ja nur einen Parameter (nämlich die eine XML-Spalte in jeder Zeile). Auch wenn man vielleicht vermuten könnte, dass xmlconcat
| doch mehr können sollte als der simple | Operator, arbeitet doch xmlconcat |
|---|
nur zeilenweise, es ist nämlich eine normale und keine Aggregatfunktion.
Das Gegenstück, dass über mehrere Zeilen xml Daten "aggregieren" kann, nennt sich xmlagg, Wobei die Aggregierunsfunktionalität da äußerst schlicht daherkommt, denn mehr als die Strings aneinander zu hängen, wird dabei nicht gemacht.
Etwas komplexeres XML (mit mehreren Hierarchie-Leveln und mehreren Child-Nodes auf einer Ebene, lässt sich relativ einfach durch einen XML export einer Tabelle zusammen bauen:
insert into test_xml (_id, xmldata) select 3, table_to_xml('zeit', false, false, '');
xpath() gibt ein Array von XML Nodes zurück. Um das in etwas brauchbar relationales umzuwandeln, muss es noch an die unnest() Funktion weitergereicht werden. Ohne das unnest() wäre am Ende der Result Tabelle nur ein (1 row) zu sehen. So sind es 8:
==> select unnest(xpath('/zeit/row/refill', xmldata)) as x_path from test_xml where _id = 3;
x_path
--------------------
<refill>1</refill>
<refill>2</refill>
<refill>3</refill>
<refill>4</refill>
<refill>5</refill>
<refill>6</refill>
<refill>7</refill>
<refill>8</refill>
(8 rows)
Außer table_to_xml() gibt es auch eine table_to_xmlschema() Funktion, die die gleichen Parameter akkzeptiert.
<xsd:simpleType name="INTEGER"> <xsd:restriction base="xsd:int"> <xsd:maxInclusive value="2147483647"/> <xsd:minInclusive value="-2147483648"/> </xsd:restriction> </xsd:simpleType> <xsd:simpleType name="DATE"> <xsd:restriction base="xsd:date"> <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> </xsd:restriction> </xsd:simpleType> <xsd:complexType name="RowType.wg_drinking.public.zeit"> <xsd:sequence> <xsd:element name="refill" type="INTEGER" minOccurs="0"></xsd:element> <xsd:element name="d_from" type="DATE" minOccurs="0"></xsd:element> <xsd:element name="d_until" type="DATE" minOccurs="0"></xsd:element> <xsd:element name="persons" type="INTEGER" minOccurs="0"></xsd:element> </xsd:sequence> </xsd:complexType>
Solange das zu verarbeitende XML keine Namespace Angaben nutzt funktioniert auch alles intuitiv. Knifflig wird es, wenn das Root Element ein Namepace Attribut hat:
<project name="MyProject" xmlns="http://nant.sf.net/release/0.92/nant.xsd"> <!-- A NAnt build file with namespace, but no namespace prefix --> </project> <zeit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <!-- table export to xml with: table_to_xml() uses xsi namespace prefix --> </zeit>
Wenn der Namespace ohne explizites Prefix, also als default Namespace deklariert ist, greift er für alle Nodes und muss deshalb auch in der XPATH Expression mit angegeben werden. Das Prefix muss dabei nicht identisch mit dem im XML verwendeten Prefix sein. Aber ohne ein Prefix geht es nicht. Also nimmt man ein denkbar kurzes, das nur aus einem Buchstaben n besteht.
Der Namespace selber ist in einem verschachtelten Array anzugeben:
SELECT xpath('/n:project/n:property/@name', xmldata, ARRAY[ARRAY['n','http://nant.sf.net/release/0.92/nant.xsd']]) as target_names FROM test_xml WHERE _id = 4; target_names ------------------------------------------------------------------- {debug.mode,nunit.path,nunit.console,assembly.name,assembly.path} (1 row) -- ... and with an additional unnest(...) around the xpath expression: target_names --------------- debug.mode nunit.path nunit.console assembly.name assembly.path (5 rows)
Window Funktionen sind so etwas wie aufgebohrte Aggregatfunktionen. In gewisser Weise machen sie innerhalb von SQL greifbar, was sonst eines der typischen Report-Tools erfordert hätte. Es sollen die einzelnen Details und auch Zwischensummen ausgegeben werden. In Oracle's SQL*Plus wird das durch ein COMPUTE zusammen mit BREAK ON erreicht. Aber das sind keine SQL Statements sondern Funktionalitäten der Reporting Engine von SQL*Plus.
Wollte man das, was das Reporting Tool macht, in eine SQL-Abfrage stecken, wäre entweder eine JOIN oder eine UNION ALL (mit etlichen Dummy-Spalten) notwendig. Die Window Funktion OVER() vereinfacht das deutlich:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) as avg FROM empsalary;
depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 -----------+-------+--------+----------------------- personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 -----------+-------+--------+----------------------- sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
Wie man leicht sieht, könnte man sich diese Darstellung auch zu Fuß beschaffen:
SELECT a.depname, a.empno, a.salary, b.avg_salary FROM empsalary a INNER JOIN (SELECT depname, avg(salary) AS avg_salary FROM empsalary GROUP BY depname) b ON a.depname = b.depname;
So richtig schön ist das aber nicht.
OVER() kann auch ganz gut ohne Parameter, also ohne die PARTITION BY Spezifikation auskommmen. In dem Fall werden eben keine Teilsummen bzw. Teil-Aggregate gebildet, sondern eben über alle Records, die von der jeweiligen WHERE-Klausel erfasst werden, aggregiert.
Mit einer Common Table Expression und dem Values Row Constructor, der ja
eigentlich für ein einfaches INSERT gedacht ist, lässt sich recht schnell
und kompakt die Verteilung der Würfelsummen beim Würfeln mit 2 Würfeln
darstellen. Der innere CROSS JOIN hätte klassisch Kreuzprodukt gemäß 6x6 also
36 Zeilen. Die Gruppierung reduziert das dankenswerterweise auf 11 Zeilen.
WITH cte AS ( SELECT n FROM (VALUES (1),(2),(3),(4),(5),(6)) nums(n) ) SELECT sum_n, count(*) FROM (SELECT a.n, b.n, (a.n + b.n) AS sum_n FROM cte a CROSS JOIN cte b) x GROUP BY sum_n ORDER BY sum_n;
sum_n | count
-------+-------
2 | 1
3 | 2
4 | 3
5 | 4
6 | 5
7 | 6
8 | 5
9 | 4
10 | 3
11 | 2
12 | 1
(11 rows)
CTEs sind so eine Art adhoc View, die man auch explizit als View hätte anlegen
können. Aber wozu, denn unter Umständen hätte man später eine one_to_six, eine
one_to_ten und eine zero_to_nine View, die danach unnütz im Datenschema herum
liegen. Noch wichtiger ist allerdings, dass eine CTE auch der benutzen kann,
der keine CREATE Berechtigung hat.
Der Vollständigkeit halber und um zu zeigen, dass die abschließende
Gruppierung auch ohne Subselect auskommen kann, kommt jetzt aber doch
noch die konventionelle Variante. Dabei sieht man auch, dass der
Multirow Konstruktor VALUES eigentlich nur eine clevere Kurzform für ein
UNION SELECT ist. Nur eine Komma zu schreiben, statt dem länglichen
UNION ALL SELECT ist schon eine merklicher Erleichterung und in der kompakteren
Form ist das Konstrukt auch einfacher zu verstehen.
CREATE VIEW one_two_six AS SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6; SELECT (a.n + b.n) AS sum_n, count(*) FROM one_to_six a CROSS JOIN one_to_six b GROUP BY sum_n ORDER BY sum_n;
Ein versierter PostgreSQL User hätte in so einem Fall auf die
Generator-Funktion generate_series zurück gegriffen. Aber da
generate_series in einem Join nicht direkt benutzt werden kann (obwohl der
CROSS JOIN keine Join-Bedingung hat) muss doch wieder eine CTE verwendet
werden:
SELECT a.generate_series + b.generate_series AS sum_n, count(*) FROM generate_series(1,6) a CROSS JOIN generate_series(1,6) b GROUP BY sum_n ORDER BY sum_n; template1-> ERROR: column a.generate_series does not exist LINE 1: SELECT a.generate_series + b.generate_series AS sum_n, count... WITH cte AS ( SELECT generate_series AS n FROM generate_series(1,6) ) SELECT (a.n + b.n) AS sum_n, count(*) FROM cte a CROSS JOIN cte b GROUP BY sum_n ORDER BY sum_n;
Der eigentliche Fehler liegt aber nicht bei generate_series sondern, daran
dass nur der einfache Tabellen-Alias benutzt wurde, wo es einen gebraucht
hätte, der analog zum obigen Alias vom VALUES Constructor sowohl Tabellen-
als auch Spaltenname angibt.
SELECT a.n + b.n AS sum_n, count(*) FROM generate_series(1,6) a(n) CROSS JOIN generate_series(1,6) b(n) GROUP BY sum_n ORDER BY sum_n;
Analog zu "load data infile ..." von MySQL gibt es den "COPY ... FROM" Befehl in Postgres Allerdings frisst der nur alle Zeilen der Import-Datei oder gar keine, was wohl an dem unterschiedlichen Transaktions-Verständnis von Postgres und MySQL liegt.
COPY table_to_import FROM '/absolute/path/on/server/data.dat' USING DELIMITERS ';' WITH NULL AS '\N';
Wenn "using" nicht mit angegeben wird, werden TAB as Delimiter und \N als NULL verwendet. Wenn die Daten vom Client kommen sollen, gibt es die Möglichkeit als file literal stdin anzugeben, dann wird direkt vom Terminal gelesen. Um von der Dateneingabe wieder zum SQL-Prompt zurück zu kommen muss die Special Sequenz \. auf einer Zeile für sich eingeben werden:
COPY table_to_import FROM stdin USING DELIMITERS ';'; 1;Text in Spalte-1 2; mehr Text \.
Der interne psql command \copy kann mit der gleichen Syntax wie COPY benutzt werden (allerdings ohne Semicolon am Ende, es ist ja kein SQL-Statement, das zum Server geschickt werden muss). Bei \copy wird der Dateiname mit den Zugriffsrechten des psql-Users auf dem Client evaluiert.
Bei neueren PostgreSQl Versionen (ab 9) ist es allerdings nicht unbedingt
notwendig csv Daten zu importieren, da die Datenbank mit Hilfe einer Extension
auch direkt von Filesystem Dateien mit tabellen-artigem Inhalt lesen kann.
Auch da gilt wiederum ebenso wie beim COPY Statement, dass der User vom
Server-Prozess Zugriffsrechte auf die Dateien benötigt und die Dateien müssen
auch auf dem Datenbank-Server liegen. Falls nur gelesen wird und daher kein
Locking notwendig ist, wäre auch eine Datei auf einem eingehängten NFS-Pfad
denkbar.
Auf der offiziellen Dokumentations-Seite von file_fdw
http://www.postgresql.org/docs/9.1/static/file-fdw.html wird denn auch oft
genug auf COPY Bezug genommen, was nun stark darauf hindeutet, dass da unter
der Haube tatsächlich nur COPY am werkeln ist.
Auch wenn die FDWs wie Tabellen aussehen, können sie in psql dennoch nicht mit \dt+ angezeigt werden, sondern brauchen ein \det+, wobei das eingefügte e wohl für external steht.
# CREATE EXTENSION file_fdw; ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/file_fdw.control": No such file or directory
In diesem Fall konnte die Extension nicht angelegt werden, weil dazu bei einem Debian System erst das Package postgresql-contrib-9.1 installiert sein muss.
CREATE SERVER local_files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE sipgate_calls (
calltime TIMESTAMP,
direction INTEGER,
other_no VARCHAR(24),
this_no VARCHAR(24)
)
SERVER local_files
OPTIONS ( format 'csv', header 'true',
filename '/home/rolf/data/sipgate-all.dat',
delimiter E'\t', null '\N' );
Das Escaping ist allerdings ein bisschen gewöhnungsbedürftig. Beim delimiter
ist nur ein einzelnes Zeichen erlaubt und \t ist ja auch ein TAB. Postgres
sieht darin allerdings zwei Zeichen (ein Backslash und ein t) falls man ihm
nicht durch ein vorangestelltes großes E (für Escape) explizit sagt, dass der
Backslash als Escape-Zeichen zu verstehen ist.
Beim Zeichen für NULL ist es gerade umgekehrt. Da stehen in der Datei ja tatsächlich die beiden Zeichen Backslash und N. Von daher darf da kein E davor gesetzt werden.
Analog zu COPY .. FROM gibt es COPY .. TO für den export (und es muss wirklich nur FROM durch TO ersetzt werden, mehr nicht!)
Das Hauptproblem, weshalb man diesen Befehl eher selten nutzen wird, ist,
dass der User, der den Postgres-Server Prozess ausführt, Schreibrechte auf
die angegebene Datei haben muss. Und dass nur der (oder die) Superuser
COPY ... TO ausführen dürfen.
Sinnvoll nutzbar ist das also nur, wenn man ins temp Verzeichnis schreibt,
dann hat mal allerdings wiederum das Problem, das man einige Handstände
machen muss um als normaler User, die Datei im /tmp Verzeichnis lesen zu
können, da die ja wegen dem t-flag nur für postgres selber und für root
lesbar ist.
UNION verhindert normalerweise die Anzeige von Duplikaten. Zeilen, die also in beiden Tabellen vorkommen, werden nur einmal angezeigt. Nicht so bei UNION ALL, da werden alle Zeilen genommen, wie sie kommen, was bedeutet, dass es im Vergleich zu UNION schneller sein sollte.
EXCEPT ist das postgres-Äquivalent zum MINUS Operator bei Oracle. INTERSECT ist genau das gleiche wie bei Oracle und dem SQL-Standard.
Transactionen werden bei postgres in einen Block von
BEGIN WORK;
update ... insert ...
COMMIT WORK;
eingeschlossen.
"BEGIN WORK" scheint auch tatsächlich das Signalwort dafür zu sein, das übliche auto-COMMIT nicht mehr für jedes einzelne Statement durchzuführen. "BEGIN WORK" startet also eine eigene Transaction in dem es das bisherige autocommit abschaltet.
Zustand der Datenbank am Anfang einer Transaction eingefroren. Soll heißen, ich sehe innerhalb der eigenen laufenden Transaction keine Änderungen von anderen egal ob die "commited" sind oder nicht. Erst wenn ich die eigene Transaction mit COMMIT oder ROLLBACK abschließe, sehe ich auch etwaige Änderungen von anderen.
Änderungen von anderen werden zu jedem Zeitpunkt berücksichtigt.
SELECT ... FOR UPDATE kann bei postgres in normalem SQL verwendet werden,
ist also nicht an einen Cursor gebunden.
um durch geschicktere Anordnung der Daten eine höhere Performanz zu erreichen.
Wird das Schlüsselwort LIMIT an das Ende einer Select-Anweisung angehängt, kann das Ergebnis der Abfrage auf die mit LIMIT angegebenen Zeilen beschränkt werden. Die ersten Zeilen eines Ergebnisses lassen sich überspringen, wenn man außer LIMIT auch OFFSET mit einer Zahl angibt.
Das ergibt allerdings nur dann konsistente Ergebnisse, wenn die Abfrage mit einem "order by" versehen wird, denn ansonsten wird das LIMIT auf die zufällige physikalische Anordnung auf dem Speichermedium angewandt. Das bedeutet, dass die Datenbank-Engine sogar noch mehr Last bekommt, denn sie muss ja für ein zuverlässiges "order by" auf jeden Fall alle Zeilen der Tabelle anschauen. (Wenn es auf die order-by Spalte einen Index gibt, mag das etwas günstiger aussehen). Lediglich zum Client werden weniger Daten/Bytes geschickt.
Beispiel:
SELECT * FROM table_a ORDER BY col_a LIMIT 3 OFFSET 3;
überspringt die ersten 3 Zeilen und gibt dann Zeile 4 bis 6 aus.
CREATE SEQUENCE any_sequence;
SELECT next_val('any_sequence'); oder curr_val()
und zum setzen:
SELECT set_val('any_sequence', 100);
Für klassische auto-increment Spalten gibt es einen eigenen Datentyp: SERIAL
col_auto SERIAL
ACHTUNG: Die Rechte-Vergabe bei postgresql ist etwas eigen. Bei Sequenzen darf der Typ sequence nicht mit genannt werden, und zum Nutzen der Sequenz braucht es neben select auch insert und update Rechte. also:
GRANT select,insert,update ON any_sequence to other_user;
und nicht:
GRANT select ON SEQUENCE any_sequence to other_user;
Cursor werden so festgelegt:
DECLARE CURSOR curs_name FOR select-anweisung
Im Gegensatz zu Oracle müssen die Cursor nicht explizit geöffnet werden, sondern sie können gleich mit FETCH abgefragt werden. Auch braucht es keine Variable, in die hinein "gefetcht" werden müsste. Das wiederum hat zur Folge, dass ich nicht an das Abfragen von einzelnen Zeilen gebunden bin. Mit
FETCH 2 FROM curs_name;
lassen sich gleich 2 Records/Treffer auf einmal auslesen. Die Records werden direkt ins Terminal-Fenster ausgegeben, als hätte man ein Select eingegeben, das durch eine geschickte Where-Klausel auf eben diese beiden Zeilen beschränkt wurde.
Allerdings muss der Cursor (wie bei Oracle) am Ende geschlossen werden:
CLOSE curs_name
Genauso wie die Cursor kann auch die RETURNING Funktionalität bei PostgreSQL direkt in SQL verwendet werden, während es bei Oracle ein INTO und PL/SQL oder Bind-Variablen braucht, um die Rückgabewerte abzulegen. PostgreSQL gibt die Daten als n x m Result-Set direkt aus. Das ist ein analoges Verhalten, wie man es von Funktionen, die ein SET zurückgeben, kennt.
Temporäre Tabellen bestehen nur während einer Session, sie können auf 2 Arten angelegt werden:
create TEMPORARY table tab_name ( col_A type); oder select into TEMPORARY table_name FROM ...
Es gibt INSTEAD rules, die die eigentliche Aktion in dem SQL-Statement ersetzen, und einfache DO rules, die zusätzlich zum Statement noch Aktionen ausführen.
CREATE RULE rule_name AS ON INSERT TO table_name DO INSTEAD nothing; CREATE RULE rule_name AS ON UPDATE TO table_name DO INSERT INTO log_table_name values (old.col_a, old.col_b, 'some Text');
table_name kann auch eine View sein, die durch solche INSTEAD rules updatable gemacht werden können.
Alle Deaktivieren mit: SET CONSTRAINTS ALL deferred;
Serverseitige Funktionen können in verschiedenen Sprachen umgesetzt werden. Language 'SQL' geht immer (bei jeder Installation), tut aber eigentlich nichts anderes als eine gespeicherte Kurzform (ein Alias, ein Makro) für ein Konstrukt darzustellen, dass man auch direkt in die SQL-Anweisung hätte eintippen können.
CREATE FUNCTION getstatename(text) RETURNS text AS 'SELECT CAST(name AS TEXT) FROM statename WHERE code = $1;' LANGUAGE 'sql';
$1 bis $9 dienen als Platzhalter für die im Funktionskopf anonym per Typ definierten Übergabeparameter. Mögliche Aufrufe der obigen Funktion wären z.B.
SELECT getstatename('AL'); -- oder SELECT c.name, getstatename(c.name) FROM customer c ORDER BY c.name;
Weitaus flexibler, weil tatsächlich prozedural ist Pl/pgsql. Um diesen Part zu verwenden, muss er allerdings erst auf der Datenbank installiert werden. Das passiert von der Shell aus mit:
createlang plpgsql <dbname>
Wenn das mit template1 als dbname ausgeführt wird, dann erben alle später angelegten Datenbanken die plpgsql-Funktionalität automatisch.
Eine schon etwas kompliziertere Funktion könnte so aus sehen:
CREATE FUNCTION getstatecode(text) RETURNS text AS 'DECLARE state_str statename.name%TYPE; statename_rec record; -- or: statename_rec statename%ROWTYPE i integer; len integer; matches record; search_str text; BEGIN state_str := initcap($1); len := length(trim($1)); i := 2; SELECT INTO statename_rec * FROM statename WHERE name = state_str; IF FOUND THEN RETURN statename_rec.code; END IF; WHILE i <= len LOOP search_str := trim( substr(state_str,1,i) ) || ''%''; SELECT INTO matches COUNT(*) FROM statename WHERE name LIKE search_str; IF matches.count = 0 THEN RETURN NULL; END IF; IF matches.count = 1 THEN SELECT INTO statename_rec * FROM statename WHERE name LIKE search_str; IF FOUND THEN RETURN statename.code; END IF; END IF; i := i + 2; END LOOP; RETURN ''''; END;' LANGUAGE 'plpgsql';
(Oracle user sind es gewohnt, nach dem SELECT erst die Spaltenliste anzugeben. In PostgreSQL sollte aber direkt nach dem SELECT schon das INTO stehen. Momentan (pg 8.1) akzeptiert Postgres noch die Oracle Syntax, aber das kann sich mit späteren Versionen ändern.)
Die Parameter werden später im Block mit $1 bis $16 angegeben, allerdings kann man das auch ein bisschen lesbarer gestalten, indem man im DECLARE-Block einen "named Alias" für die "Positions-Parameter" $1, ... anlegt.
DECLARE p_num_items ALIAS FOR $1; BEGIN ....
Etwas ärgerlich ist dabei, dass der komplette Funktionsrumpf in SQL String Delimitern stehen muss, also vor DECLARE und nach END; ein öffnendes bzw. schließendes Quote steht. Das hat nämlich zur Folge, dass alle Single- Quotes im Funktionsrumpf durch ein zweites vorangestelltes Quote maskiert werden müssen. Das macht die Sache merklich unleserlicher.
Seit irgendeiner 7.x Version kann man sich das Leben einfacher machen und den Code string anstatt mit einfachen Quotes mit $$ als Delimiter schreiben. Damit entfällt das umständliche Quoten von einfachen Quotes. In der Docu findet man das allerdings nicht. Ich habe das zuerst in einigen Foren gesehen, weil die Teilnehmer dort aber wohl allesamt pgAccess benutzt haben, nahm ich aber an, dass das nur in pgAccess möglich ist und dass dort das benötigte Quoting intern abläuft, bevor die Daten an den Server geschickt werden. Aber es geht auch im einfachen psql Interface, weil die Verarbeitung eben doch im Server passiert. Der springende Punkt dabei ist, ob es da undokumentiert, ab Version 9.x plötzlich nicht mehr geht!
CREATE OR REPLACE FUNCTION get_two( integer) RETURNS integer as $$ BEGIN RETURN 2; END; $$ LANGUAGE plpgsql;
Um sich alle selbst erstellten Funktionen auflisten zu lassen, gibt es in psql einen weiteren \d Backslash Befehl: \df Allerdings spukt der auch noch alle nutzbaren Funktionen aus dem default Katalog pg_catalog aus, und man ist dann am Stecknadel im Heuhaufen suchen. Hilfreich ist da das optionale Pattern, das man noch mit angegeben kann und mit dem man außer generell zu filtern auch auf ein bestimmtes Schema filtern kann:
\df public.*
Da postgresql Funktionen kennt, die record sets zurückgegeben (RETURN NEXT bzw. RETURN QUERY), lassen sich Zahlenfolgen mit einer eingebauten Funktion relativ schnell erzeugen. In anderen Datenbanken-Systemen hätte man da eine Hilfstabelle gebraucht:
SELECT * FROM generate_series(1,10); SELECT * FROM generate_series(1,10,2);
Der dritte Parameter ist übrigens ein Skip Modifizierer. Allerdings lässt sich das ganze nur ganz schlecht in eine Query mit join verpacken, was ja in den meisten Fällen das ist, was man haben will: "Ich brauche hier 10 aufeinander folgende Tage ..."
select now() AS a, generate_series AS b from generate_series(1,10,3);
a | b
-------------------------------+----
2010-04-27 12:13:40.672716+02 | 1
2010-04-27 12:13:40.672716+02 | 4
2010-04-27 12:13:40.672716+02 | 7
2010-04-27 12:13:40.672716+02 | 10
(4 rows)
generate_series() muss eigentlich immer in ein sub-select verpackt werden, wenn es in einem Join benutzt werden soll:
SELECT table_a.*, serials.i FROM table_a, (select generate_series AS i from generate_series(1,20,2)) serials WHERE table_a.id = serials.i;
Seit Version 7.2 gibt es einige spezielle Funktionen die den postgreSQL internen Katalog auswerten:
Andere Server-seitige Erweiterungen zu benutzen, etwa PL/tcl oder PL/Perl, ist meiner Meinung nach nur von beschränktem Nutzen, da mit diesen Sprachen nur einfache scalare Typen als Returnwerte der eigenen Funktionen erlaubt sind. Mit PL/pgSQL kann man dagegen record types zurück geben und mittels "RETURN NEXT" auch mehrere Zeilen als result set.
Seit Version 9.0 lassen sich mit PostgreSQL auch anonyme prozedurale Blöcke einsetzen. Interessanterweise hat der der Type der prozeduralen Sprache, die genutzt wird schon am Anfang vor dem Code-Block zu stehen, während er bei Funktionen ja erst am Ende kommt, nach dem END und dem abschließenden Delimiter.
DO LANGUAGE plpgsql $$ DECLARE r record; BEGIN FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema = 'public' LOOP EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser'; END LOOP; END$$;
Trigger werden in PostgreSQL nicht im globalen Namespace abgelegt, sondern immer im Kontext der Tabelle, auf die sie wirken. Die CREATE-Syntax ist zwar die gleiche wie bei Oracle, aber beim Löschen des Triggers muss konsequenterweise auch der Tabellenname mit angegeben werden:
DROP TRIGGER my_triger ON my_table_with_trigger;
Um einen Trigger zu installieren braucht es eine Funktion ohne Parameter, die den speziellen Return-Typ trigger hat:
CREATE FUNCTION emp_stamp() RETURNS trigger AS $$BEGIN -- Check that salary is given IF NEW.salary IS NULL THEN RAISE EXCEPTION '% cannot have null salary', NEW.empname; END IF; NEW.last_date := 'now'; NEW.last_user := current_user; RETURN NEW; END;$$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Außer der automatischen Trigger-Struktur die als Parameter an die Trigger-Funktion durch gereicht wird, gibt es auch eine Pseudo-Variable TG_OP, die den Statement Mode angibt:
IF TG_OP = 'DELETE' THEN ... ELSIF TG_OP = 'UPDATE' THEN ... ELSIF TG_OP = 'INSERT' THEN ... END IF;
Oracle Programmierer müssen umdenken. Es gibt zwar die Pseudo Variablen new und old, aber bei PostgreSQL werden die ohne vorangestellten Doppelpunkt referenziert.
Um vom Triggernamen auf die Tabelle zu kommen, die dazu gebraucht wird, muss eine Abfrage auf die pg_-Systemtabellen gemacht werden:
SELECT relname FROM pg_class INNER JOIN pg_trigger ON (tgrelid = relfilenode) WHERE tgname = 'my_trigger';
Vor PostgreSQL 9.1 waren nur Rules (eine Postgresql Eigenheit) erlaubt, um festzulegen, wie eine View beschrieben werden kann. Seit 9.1 ist das auch über INSTEAD OF Trigger möglich und auch der bevorzugte Weg:
CREATE VIEW emp_view AS SELECT e.empname, e.salary, max(ea.stamp) AS last_updated FROM emp e LEFT JOIN emp_audit ea ON ea.empname = e.empname GROUP BY 1, 2; CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$ BEGIN -- -- Perform the required operation on emp, and create a row in emp_audit -- to reflect the change made to emp. -- IF (TG_OP = 'DELETE') THEN DELETE FROM emp WHERE empname = OLD.empname; IF NOT FOUND THEN RETURN NULL; END IF; OLD.last_updated = now(); INSERT INTO emp_audit VALUES('D', user, OLD.*); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname; IF NOT FOUND THEN RETURN NULL; END IF; NEW.last_updated = now(); INSERT INTO emp_audit VALUES('U', user, NEW.*); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp VALUES(NEW.empname, NEW.salary); NEW.last_updated = now(); INSERT INTO emp_audit VALUES('I', user, NEW.*); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; /* Deckt alle 3 Events INSERT, UPDATE und DELETE ab ... */ CREATE TRIGGER emp_audit INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view FOR EACH ROW EXECUTE PROCEDURE update_emp_view();
(Bei PostgreSQL werden Tabellennamen — wenn sie beim Anlegen nicht in Anführungszeichen angegeben worden sind — automatisch als lowercase abgelegt. Auch das ist ein Verhalten, das dem von Oracle gerade entgegengesetzt ist.)
$conn = pg_connect("dbname=$dbname host=$host"); ... pg_exec($conn, "set datestyle to german"); $res = pg_query($conn, "select * from my_table"); $rows = pg_numrows($res); for ($i=0; $i < $rows; $i++) { $data = pg_fetch_array($res, $i); }