PostgreSQL Datenbankpraxis für Anwender, Administratoren und Entwickler

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);