Beispieltabelle für Fließkommazahlen
CREATE TABLE rt (zahl REAL);
INSERT INTO rt (zahl) VALUES (NULL);
SELECT zahl FROM rt;
Mit NULL vergleichen
SELECT zahl FROM rt WHERE zahl IS NULL;
Unbekannte und nicht definierte Werte
INSERT INTO rt (zahl) VALUES ('Infinity');
INSERT INTO rt (zahl) VALUES ('-Infinity');
INSERT INTO rt (zahl) VALUES ('NaN');
SELECT zahl FROM rt;
Unbekannte und nicht definierte Werte abfragen
SELECT zahl FROM rt WHERE zahl = 'NaN';
Zu viele Nachkommastellen
CREATE TABLE ft (zahl NUMERIC(5, 2));
Zu viele Nachkommastellen
INSERT INTO ft (zahl) VALUES (500.00);
INSERT INTO ft (zahl) VALUES (999.12345);
SELECT zahl FROM ft;
Anzahl Reiskörner auf einem Schachbrett
SELECT power(2::DOUBLE PRECISION, 64::DOUBLE PRECISION) - 1;
SELECT power(2::NUMERIC, 64::NUMERIC) - 1;
Reiskörner zählen in PostgreSQL 8.4
SELECT feld, koerner_einzeln, SUM(koerner_einzeln) OVER (ORDER BY feld) AS koerner_summe FROM ( WITH RECURSIVE reis AS (SELECT 1 AS feld, 1::DECIMAL AS koerner_einzeln UNION ALL SELECT feld + 1, koerner_einzeln * 2 FROM reis WHERE feld < 64) SELECT * FROM reis) AS rekursion;
Mit bestem Dank an Andreas Kretschmer für dieses schöne Beispiel.
Casten von Zahlen
CREATE TABLE zahlen_test (id INTEGER);
INSERT INTO zahlen_test (id) VALUES (23);
SELECT id FROM zahlen_test;
Casten von Zahlen
INSERT INTO zahlen_test (id) VALUES ('42');
SELECT id FROM zahlen_test;
Casten in einen anderen Datentyp
SELECT 23::TEXT;
SELECT 23::INTEGER;
Gecastete Daten
SELECT 23::TEXT + 42::INTEGER;
Alternative Notation eines CAST
SELECT CAST(23 AS TEXT);
Vergleiche mit Text-Datentypen
SELECT 1 WHERE 'a '::VARCHAR = 'a'::VARCHAR;
SELECT 1 WHERE 'a'::VARCHAR = 'a'::VARCHAR;
SELECT 1 WHERE 'a '::CHAR = 'a'::CHAR;
SELECT 1 WHERE 'a '::CHAR = 'a'::VARCHAR;
Binäre Daten speichern und auslesen
CREATE TABLE bytea_test (id SERIAL, daten BYTEA);
INSERT INTO bytea_test (daten) VALUES (E'Ein \\000 Byte');
Binäre Daten speichern und auslesen
INSERT INTO bytea_test (daten) VALUES (E'Ein Backslash: \\\\');
INSERT INTO bytea_test (daten) VALUES (E'Ein Hochkomma: \'');
SELECT id, daten FROM bytea_test;
Hochkommas ohne Escape-Syntax
select 'what\'s up?';
Eine Auswahl möglicher Zeitangaben
SELECT '2008-10-11'::TIMESTAMP;
SELECT 'January 5 2007'::TIMESTAMP;
SELECT '2005-08-10 03:25:00PM UTC'::TIMESTAMPTZ;
SELECT '2008-12-12 10:23:00 UTC'::TIMESTAMPTZ;
Uhrzeit mit Zeitzone
SELECT '2008-04-12 00:00:00 Europe/Moscow'::TIMESTAMPTZ;
SELECT '2008-04-12 00:00:00 +4'::TIMESTAMPTZ;
Zeitzone ändern
BEGIN;
SELECT NOW();
SET TIME ZONE 'Europe/Moscow';
SELECT NOW();
ROLLBACK;
Uhrzeit für eine andere Zeitzone angeben
SELECT NOW() AT TIME ZONE 'Europe/Moscow';
Berechnungen mit Uhrzeiten
SELECT '2000-01-05'::TIMESTAMP - '2000-01-01'::TIMESTAMP;
SELECT '2000-01-01'::TIMESTAMP - '2000-01-04'::TIMESTAMP;
\ Berechnungen mit Uhrzeiten
SELECT '2005-10-23 00:23:12'::TIMESTAMP - '2005-10-12 07:05:25'::TIMESTAMP;
Datum plus ein Tag
SELECT '2000-02-28 00:00:00'::TIMESTAMP + INTERVAL '1 day 02:00:00';
Anzahl der Tage im Jahr berechnen
SELECT '2001-01-01'::DATE - '2000-01-01'::DATE;
SELECT '2002-01-01'::DATE - '2001-01-01'::DATE;
Die Uhrzeit in Neuseeland
SELECT NOW() AT TIME ZONE 'NZ';
Die Uhrzeit am Südpol
SELECT NOW() AT TIME ZONE 'Antarctica/South_Pole';
Boolesche Werte
SELECT true::BOOLEAN;
SELECT false::BOOLEAN;
Boolesche Werte in PHP
$ergebnis = ... TRUE oder FALSE von PostgreSQL if ($ergebnis) ...
ENUM-Datentyp für Skatfarben
CREATE TYPE skat_farben AS ENUM ('Karo', 'Herz', 'Pik', 'Kreuz');
ENUM-Datentyp verwenden
CREATE TABLE skat_ergebnisse ( id SERIAL PRIMARY KEY, farbe skat_farben NOT NULL, ... );
ENUM-Datentyp verwenden
INSERT INTO skat_ergebnisse (farbe) VALUES ('Herz');
INSERT INTO skat_ergebnisse (farbe) VALUES ('Pik');
ENUM-Datentyp in einem Vergleich nutzen
SELECT id, farbe FROM skat_ergebnisse WHERE farbe = 'Herz';
ENUM-Datentyp beim Sortieren
INSERT INTO skat_ergebnisse (farbe) VALUES ('Karo');
SELECT id, farbe FROM skat_ergebnisse ORDER BY darbe;
Beispieldatentyp zum Ändern des Datentyps
CREATE TYPE t_farben AS ENUM ('rot', 'blau', 'gelb');
CREATE TABLE farb_test (id SERIAL, farbe T_FARBEN);
INSERT INTO farb_test (farbe) VALUES ('blau');
INSERT INTO farb_test (farbe) VALUES ('gelb');
INSERT INTO farb_test (farbe) VALUES ('blau');
Beispieltabelle
CREATE TABLE farben (farbe VARCHAR);
INSERT INTO farben (farbe) VALUES ('rot');
INSERT INTO farben (farbe) VALUES ('blau');
INSERT INTO farben (farbe) VALUES ('gelb');
INSERT INTO farben (farbe) VALUES ('grün');
Datentyp ändern
ALTER TABLE farben ALTER COLUMN farbe TYPE VARCHAR;
ALTER TABLE farben ALTER COLUMN farbe SET NOT NULL;
ALTER TABLE farben ADD CONSTRAINT farben_enum FOREIGN KEY (farbe) REFERENCES farben(farbe);
Datentyp löschen
DROP TYPE skat_farben;
Datentyp löschen
DROP TYPE skat_farben CASCADE;
Datentyp INET nutzen
SELECT '127.0.0.1'::INET;
SELECT '127.0.0.1/8'::INET;
Funktionen für den Datentyp INET
SELECT broadcast('192.168.23.42/27'::INET);
SELECT host(network('192.168.23.42/27'::INET));
SELECT netmask('192.168.23.42/27'::INET);
SELECT text('192.168.23.42/27'::INET);
Ermitteln des Netzwerks unter Zuhilfenahme von INET
SELECT '192.168.23.45'::INET <<= '192.168.23.42/27'::INET;
SELECT '10.0.0.1'::INET <<= '192.168.23.42/27'::INET;
Berechnungen mit IP-Adressen
SELECT '192.168.23.45'::INET - 100;
Datentyp MAC nutzen
SELECT '00:1B:38:22:84:7E'::MACADDR;
SELECT '00-1B-38-22-84-7E'::MACADDR;
SELECT '001B3822847E'::MACADDR;
Datentyp MONEY nutzen
SELECT '5849628.12'::MONEY;
Locale-Einstellungen ausgeben
SHOW lc_monetary;
Locale-Einstellungen ändern
SET lc_monetary to 'de_DE.UTF-8';
SELECT '5849628,12'::MONEY;
Nutzung von BIT-Datenfeldern
CREATE TABLE bit_test (id SERIAL, daten BIT(5));
INSERT INTO bit_test (daten) VALUES (B'10101');
SELECT id, daten FROM bit_test;
Operationen mit BIT-Datentypen
-- AND (logisch UND) SELECT id, daten & B'00001' FROM bit_test;
-- OR (logisch ODER) SELECT id, daten | B'01011' FROM bit_test;
-- XOR (logisch Exklusiv Oder) SELECT id, daten # B'11111' FROM bit_test;
-- Shift left (Bits nach Links schieben) SELECT id, daten << 2, daten FROM bit_test;
Suchen in BIT-Datentypen
SELECT id, daten FROM bit_test WHERE (daten & B'00001')::INTEGER > 0;
Suchen in BIT-Datentypen
SELECT id, daten FROM bit_test WHERE (daten & B'00010')::INTEGER > 0;
Casts zwischen BIT und INTEGER
SELECT 23::BIT(5);
SELECT B'10101'::BIT(5)::INTEGER;
SELECT X'FE'::BIT(8)::INTEGER;
XML-Text angeben
SELECT ' <Datenbanken> <Datenbank>PostgreSQL</Datenbank> </Datenbanken>'::xml;
SELECT XMLPARSE (CONTENT ' <Datenbanken> <Datenbank>PostgreSQL</Datenbank> </Datenbanken>')::xml;
XML-Typ ändern
SET xmloption TO DOCUMENT;
SET xmloption TO CONTENT;
XML-Typ ändern
SET XML OPTION TO DOCUMENT;
SET XML OPTION TO CONTENT;
Beispieltabelle für XML-Ausgabe
CREATE TABLE xml_output ( daten xml );
INSERT INTO xml_output (daten) VALUES ('<Datenbanken> <Datenbank>PostgreSQL</Datenbank> </Datenbanken>');
XML-Daten auslesen
SELECT daten::TEXT FROM xml_output;
XML-Daten auslesen
SELECT XMLSERIALIZE(CONTENT daten AS TEXT) FROM xml_output;
DOCUMENT oder doch nur CONTENT?
SELECT 1 FROM xml_output WHERE daten IS DOCUMENT;
Mehr Beispielinhalte für XML
INSERT INTO xml_output (daten) VALUES ('<Datenbanken> <Datenbank>DB2</Datenbank> <Datenbank>Oracle</Datenbank> </Datenbanken>');
Softwareprodukte suchen
SELECT xpath('/Datenbanken/Datenbank/text()', daten) FROM xml_output;
Beispieltabelle
CREATE TABLE to_xml (id SERIAL PRIMARY KEY, daten TEXT);
INSERT INTO to_xml (daten) VALUES ('Fahrrad');
INSERT INTO to_xml (daten) VALUES (NULL);
INSERT INTO to_xml (daten) VALUES ('Auto');
Tabelle als XML ausgeben
SELECT table_to_xml('to_xml', TRUE, TRUE, '');
Query als XML ausgeben
SELECT query_to_xml('SELECT id, daten FROM to_xml', TRUE, TRUE, '');
Cursor als XML ausgeben
BEGIN;
DECLARE mein_cursor CURSOR FOR SELECT id, daten FROM to_xml;
SELECT cursor_to_xml(mein_cursor, 5, TRUE, TRUE, '');
CLOSE mein_cursor;
ROLLBACK;
Tabelle mit Sequenz erstellen
CREATE TABLE serial_test ( id SERIAL PRIMARY KEY, inhalt TEXT );
Sequenz nutzen
INSERT INTO serial_test (inhalt) VALUES ('Wert a');
INSERT INTO serial_test (inhalt) VALUES ('Wert b');
SELECT id, inhalt FROM serial_test;
Sequenz manuell erstellen
CREATE SEQUENCE meine_sequenz;
Nachträglich eine Sequenz nutzen
CREATE TABLE seq_test ( id INTEGER PRIMARY KEY, daten TEXT );
CREATE SEQUENCE test_id_seq;
ALTER SEQUENCE test_id_seq OWNED BY seq_test.id;
Nachträglich eine Sequenz nutzen
ALTER TABLE seq_test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
Sequenz in mehreren Tabellen nutzen
CREATE SEQUENCE meine_sequenz;
CREATE TABLE serial_test1 ( id INTEGER UNIQUE DEFAULT NEXTVAL('meine_sequenz'), inhalt TEXT );
CREATE TABLE serial_test2 ( id INTEGER UNIQUE DEFAULT NEXTVAL('meine_sequenz'), inhalt TEXT );
Name einer Sequenz ermitteln
SELECT pg_get_serial_sequence('serial_test', 'id');
SELECT pg_get_serial_sequence('public.serial_test', 'id');
Letzte generierte ID herausfinden
INSERT INTO serial_test1 (inhalt) VALUES ('ein Eintrag');
SELECT CURRVAL('meine_sequence');
SELECT CURRVAL('meine_sequence');
Letzte generierte ID herausfinden
INSERT INTO serial_test1 (inhalt) VALUES ('ein anderer Eintrag') RETURNING id;
Rückwärts zählen
CREATE SEQUENCE herunterzaehlen INCREMENT BY -1 MAXVALUE 1000 START WITH 1000 NO CYCLE;
SELECT NEXTVAL('herunterzaehlen');
SELECT NEXTVAL('herunterzaehlen');
Große Sprünge wagen
CREATE SEQUENCE springen INCREMENT BY 5 MINVALUE 0 START WITH 0;
SELECT NEXTVAL('springen');
SELECT NEXTVAL('springen');
Sequenz zurücksetzen
CREATE SEQUENCE zuruecksetzen;
SELECT NEXTVAL('zuruecksetzen');
SELECT SETVAL('zuruecksetzen', 50);
SELECT NEXTVAL('zuruecksetzen');
Sequenz zurücksetzen
CREATE TABLE serial_test ( id SERIAL UNIQUE, inhalt TEXT );
INSERT INTO serial_test (id, inhalt) VALUES (1, 'Wert 1');
INSERT INTO serial_test (id, inhalt) VALUES (5, 'Wert 5');
INSERT INTO serial_test (id, inhalt) VALUES (20, 'Wert 20');
SELECT SETVAL('serial_test_id_seq', SELECT MAX(id) FROM serial_test;);
Ergebnis durchnummerieren
CREATE TEMPORARY SEQUENCE ergebnisse_zaehlen;
SELECT inhalt, NEXTVAL('ergebnisse_zaehlen') AS nr FROM serial_test ORDER BY id;
Ergebnis durchnummerieren mit row_number()
SELECT inhalt, row_number() OVER (ORDER BY id) AS nr FROM serial_test ORDER BY id;
Löcher in der Sequenz vermeiden
LOCK TABLE beispiel IN EXCLUSIVE MODE;
SELECT MAX(id) + 1 AS neuer_wert FROM beispiel;
Tabelle mit OIDs anlegen
CREATE TABLE oid_test (daten TEXT) WITH OIDS;
INSERT INTO oid_test (daten) VALUES ('Eintrag 1');
SELECT oid, daten FROM oid_test;
OID-Spalte abfragen
SELECT oid, id, daten FROM oid_test;
OID-Spalte abfragen
SELECT oid, * FROM oid_test;
OID mittels CAST herausfinden
SELECT 'oid_test'::regclass::oid;
OID mittels SELECT herausfinden
SELECT oid FROM pg_class WHERE relname = 'oid_test';
OIDs für weitere Objekte finden
SELECT 'avg(INTEGER)'::regprocedure::oid;
SELECT '+(INTEGER, INTEGER)'::regoperator::oid;
SELECT 'TEXT'::regtype::oid;
SELECT 'german'::regconfig::oid;
SELECT 'simple'::regdictionary::oid;
OID der Tabelle herausfinden
SELECT oid, tableoid, daten FROM oid_test;
SELECT '535189'::oid::regclass;
xmin und xmax
SELECT oid, xmin, xmax, daten FROM oid_test;
xmax kann auch größer 0 sein
BEGIN;
DELETE FROM oid_test;
SELECT oid, xmin, xmax, daten FROM oid_test;
ROLLBACK;
SELECT oid, xmin, xmax, daten FROM oid_test;
cmin ist immer vorhanden
SELECT oid, cmin, cmax, daten FROM oid_test;
Die physikalische Position in ctid
SELECT oid, ctid, daten FROM oid_test;
Arrays als Spalten
CREATE TABLE woechentlich1 (wochentag INTEGER[]);
INSERT INTO woechentlich1 (wochentag) VALUES ('{5, 7, 8, 6, 4, 0, 0}');
SELECT wochentag FROM woechentlich1;
Array mit Angabe der Dimension
CREATE TABLE woechentlich2 (wochentag INTEGER[11]);
Mehrdimensionales Array
CREATE TABLE woechentlich3 (wochentag INTEGER[][]);
INSERT INTO woechentlich3 (wochentag) VALUES ('{{5, 7, 8, 6, 4, 0, 0}, {11, 13, 18, 14, 9, 1, 0}}');
SELECT wochentag FROM woechentlich3;
ARRAY-Konstruktor
INSERT INTO woechentlich3 (wochentag) VALUES (ARRAY[[7, 8, 8, 6, 3, 0, 0], [9, 10, 12, 12, 8, 0, 0]]);
SELECT wochentag FROM woechentlich3;
SELECT ARRAY[23, 42];
Auf einzelne Arrayelemente zugreifen
SELECT wochentag[1:5][1:5] FROM woechentlich3;
In Arrays suchen
INSERT INTO woechentlich1 (wochentag) VALUES (ARRAY[7, 8, 8, 6, 3, 1, 0]);
SELECT wochentag FROM woechentlich1 WHERE wochentag[6] > 0 OR wochentag[7] > 0;
In Arrays suchen
SELECT wochentag FROM woechentlich1 WHERE 4 = ANY(wochentag);
In Arrays suchen
SELECT wochentag FROM woechentlich1 WHERE 4 = ALL(wochentag);
Arrayinhalt verändern
SELECT wochentag FROM woechentlich1 WHERE wochentag[6] > 0;
UPDATE woechentlich1 SET wochentag[6] = '0' WHERE wochentag[6] > 0;
SELECT wochentag FROM woechentlich1 WHERE wochentag[6] > 0;
Arrayinhalt löschen
SELECT wochentag FROM woechentlich1;
UPDATE woechentlich1 SET wochentag = NULL WHERE wochentag[1] = 5;
SELECT wochentag FROM woechentlich1;
SELECT array_prepend(6, ARRAY[7, 8]);
SELECT array_append(ARRAY[7, 8], 9);
Arrays zusammensetzen
SELECT array_cat('{1, 2}', ARRAY[3, 4]);
Array in Text umwandeln
SELECT array_to_string(ARRAY['Auto','Fahrrad','Bahn'], ', ');
Text in Array umwandeln
SELECT string_to_array('Auto, Fahrrad, Bahn', ', ');
Dimensionen eines Array herausfinden
SELECT array_dims(ARRAY['Auto']);
SELECT array_dims(ARRAY['Auto', 'Bahn', 'Fahrrad']);
Dimensionen eines Array herausfinden
SELECT array_dims(ARRAY[[1, 2, 4], [5, 6, 8]]);
Dimensionen eines Array herausfinden
SELECT array_ndims(ARRAY[[1, 2, 4], [5, 6, 8]]);
Grenzen eines Array herausfinden
SELECT array_lower(ARRAY[1, 2, 4], 1);
SELECT array_upper(ARRAY[1, 2, 4], 1);
Anzahl der Einträge herausfinden
SELECT array_length(ARRAY[[1, 2], [4, 5], [7, 8]], 2);
SELECT cardinality(ARRAY[[1, 2], [4, 5], [7, 8]]);
Neues Array mit Werten füllen
SELECT array_fill(23, ARRAY[5]);
Neues Array mit Werten füllen
SELECT array_fill(23, ARRAY[5]);
SELECT array_fill(42, ARRAY[5], ARRAY[3]);
Array in Zeilen zerlegen
SELECT unnest(ARRAY['Haus', 'Boot', 'Hof']);
Ergebnis in Array kumulieren
CREATE TABLE array_agg_test (daten TEXT);
INSERT INTO array_agg_test (daten) VALUES ('Haus');
INSERT INTO array_agg_test (daten) VALUES ('Boot');
INSERT INTO array_agg_test (daten) VALUES ('Hof');
SELECT array_agg(daten) FROM array_agg_test;
Zusammengesetzte Datentypen
CREATE TYPE namensfeld AS ( vorname TEXT, nachname TEXT);
Adressbuch
CREATE TABLE adressbuch ( name namensfeld, adresse TEXT);
INSERT INTO adressbuch (name, adresse) VALUES (ROW('Andreas', 'Scherbaum'), 'Magdeburg');
SELECT name, adresse FROM adressbuch;
Zusammengesetzte Datentypen abfragen
INSERT INTO adressbuch (name, adresse) VALUES (ROW('Andrea', 'Scherbaum'), 'Magdeburg');
SELECT name, adresse FROM adressbuch WHERE (name).vorname = 'Andrea';
Zusammengesetzte Datentypen in Funktionen
CREATE TYPE adresse AS ( vorname TEXT, nachname TEXT, adresse TEXT);
CREATE OR REPLACE FUNCTION suche_adressen () RETURNS SETOF adresse AS $$ DECLARE loop_record RECORD; ergebnis adresse; BEGIN FOR loop_record IN SELECT (name).vorname AS vorname, (name).nachname AS nachname, adresse FROM adressbuch ORDER BY vorname ASC LOOP ergebnis.vorname = loop_record.vorname; ergebnis.nachname = loop_record.nachname; ergebnis.adresse = loop_record.adresse; RETURN NEXT ergebnis; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql';
SELECT * FROM suche_adressen();
Einen Operator anlegen
CREATE OPERATOR < ( PROCEDURE = boollt, LEFTARG = boolean2, RIGHTARG = boolean, COMMUTATOR = >, NEGATOR = >=, RESTRICT = scalarltsel, JOIN = scalarltjoinsel );
Einen Operator löschen
DROP OPERATOR < (boolean2, boolean);
Einen Operator ändern
ALTER OPERATOR < (boolean2, boolean) OWNER to anderer_user;
Einen CAST anlegen
CREATE CAST (boolean2 AS boolean) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (boolean AS boolean2) WITHOUT FUNCTION AS IMPLICIT;
Einen CAST löschen
DROP CAST (boolean2 AS boolean);