Inhaltsverzeichnis
Kapitel 6 - Datentypen
Seite 123
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;
Seite 124
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));
Seite 125
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.
Seite 126
Casten von Zahlen
CREATE TABLE zahlen_test (id INTEGER);
INSERT INTO zahlen_test (id) VALUES (23);
SELECT id FROM zahlen_test;
Seite 127
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;
Seite 128
Alternative Notation eines CAST
SELECT CAST(23 AS TEXT);
Seite 129
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;
Seite 130
Binäre Daten speichern und auslesen
CREATE TABLE bytea_test (id SERIAL, daten BYTEA);
INSERT INTO bytea_test (daten)
VALUES (E'Ein \\000 Byte');
Seite 131
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?';
Seite 133
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;
Seite 134
Zeitzone ändern
BEGIN;
SELECT NOW();
SET TIME ZONE 'Europe/Moscow';
SELECT NOW();
ROLLBACK;
Seite 135
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';
Seite 136
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';
Seite 137
Boolesche Werte
SELECT true::BOOLEAN;
SELECT false::BOOLEAN;
Boolesche Werte in PHP
$ergebnis = ... TRUE oder FALSE von PostgreSQL if ($ergebnis) ...
Seite 138
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');
Seite 139
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');
Seite 140
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);
Seite 141
Datentyp löschen
DROP TYPE skat_farben;
Datentyp löschen
DROP TYPE skat_farben CASCADE;
Seite 142
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);
Seite 143
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;
Seite 144
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;
Seite 145
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;
Seite 146
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;
Seite 147
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;
Seite 148
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;
Seite 149
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;
Seite 150
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>');
Seite 151
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, '');
Seite 152
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;
Seite 153
Tabelle mit Sequenz erstellen
CREATE TABLE serial_test ( id SERIAL PRIMARY KEY, inhalt TEXT );
Seite 154
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;
Seite 155
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
);
Seite 156
Name einer Sequenz ermitteln
SELECT pg_get_serial_sequence('serial_test', 'id');
SELECT pg_get_serial_sequence('public.serial_test', 'id');
Seite 157
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');
Seite 158
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');
Seite 159
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;
Seite 160
Ergebnis durchnummerieren mit row_number()
SELECT inhalt,
row_number() OVER (ORDER BY id) AS nr
FROM serial_test
ORDER BY id;
Seite 161
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;
Seite 162
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';
Seite 163
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;
Seite 164
OID der Tabelle herausfinden
SELECT oid, tableoid, daten FROM oid_test;
SELECT '535189'::oid::regclass;
Seite 165
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;
Seite 166
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;
Seite 167
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;
Seite 168
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;
Seite 169
In Arrays suchen
SELECT wochentag
FROM woechentlich1
WHERE 4 = ANY(wochentag);
In Arrays suchen
SELECT wochentag
FROM woechentlich1
WHERE 4 = ALL(wochentag);
Seite 170
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;
Seite 171
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'], ', ');
Seite 172
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]]);
Seite 173
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]]);
Seite 174
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]);
Seite 175
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;
Seite 176
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';
Seite 177
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();
Seite 181
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;
Seite 182
Einen CAST anlegen
CREATE CAST (boolean2 AS boolean)
WITHOUT FUNCTION
AS IMPLICIT;
CREATE CAST (boolean AS boolean2)
WITHOUT FUNCTION
AS IMPLICIT;
Seite 183
Einen CAST löschen
DROP CAST (boolean2 AS boolean);