PostgreSQL-Datentypen

In diesem Kapitel besprechen wir PostgreSQL-Datentypen. Datentypen sind das, was wir für jedes Feld beim Erstellen einer Tabelle festlegen.

Vorteile der Festlegung von Datentypen:

PostgreSQL bietet eine Vielzahl von Datentypen. Benutzer können den Befehl CREATE TYPE verwenden, um neue Datentypen in der Datenbank zu erstellen. PostgreSQL hat viele Datentypen, und wir werden sie weiter unten im Detail erklären.

Numerische Typen

Numerische Typen bestehen aus zwei-, vier- oder acht Byte großen Ganzzahlen, vier- oder acht Byte großen Gleitkommazahlen und Dezimalzahlen mit auswählbarer Genauigkeit.

Die folgende Tabelle listet die verfügbaren numerischen Typen auf.

Monetäre Typen

Der Typ money speichert Geldbeträge mit einer festen Bruchpräzision.

Werte der Typen numeric, int und bigint können in money umgewandelt werden. Die Verwendung von Gleitkommazahlen für monetäre Typen wird nicht empfohlen, da Rundungsfehler möglich sind.

Zeichentypen

Die folgende Tabelle listet die von PostgreSQL unterstützten Zeichentypen auf:

Datum-/Zeittypen

Die folgende Tabelle listet die von PostgreSQL unterstützten Datums- und Zeittypen auf.

Boolesche Typen

PostgreSQL unterstützt den standardmäßigen booleschen Datentyp.

Der boolesche Typ hat zwei Zustände, "true" (wahr) oder "false" (falsch), und einen dritten "unknown" (unbekannt) Zustand, dargestellt durch NULL.

Aufzählungstypen

Ein Aufzählungstyp ist ein Datentyp, der eine geordnete Menge statischer Werte enthält.

Aufzählungstypen in PostgreSQL ähneln den Enum-Typen in der Sprache C.

Im Gegensatz zu anderen Typen müssen Aufzählungstypen mit dem Befehl CREATE TYPE erstellt werden.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Erstellen Sie die Wochentage wie unten gezeigt:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Wie andere Typen können Aufzählungstypen nach ihrer Erstellung in Tabellen- und Funktionsdefinitionen verwendet werden.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

Geometrische Typen

Geometrische Datentypen repräsentieren zweidimensionale planare Objekte.

Die folgende Tabelle listet die von PostgreSQL unterstützten geometrischen Typen auf.

Der grundlegendste Typ: point. Er ist die Grundlage der anderen Typen.

Netzwerkadresstypen

PostgreSQL bietet Datentypen zum Speichern von IPv4-, IPv6- und MAC-Adressen.

Das Speichern von Netzwerkadressen mit diesen Datentypen ist besser als die Verwendung von reinen Texttypen, da diese Typen Eingabefehlerprüfung sowie spezielle Operationen und Funktionen bieten.

Beim Sortieren der Datentypen inet oder cidr werden IPv4-Adressen immer vor IPv6-Adressen sortiert, einschließlich IPv4-Adressen, die in IPv6-Adressen gekapselt oder abgebildet sind, wie ::10.2.3.4 oder ::ffff:10.4.3.2.

Bit-String-Typen

Ein Bit-String ist eine Zeichenkette aus 1en und 0en. Sie können verwendet werden, um Bitmasken zu speichern und zu visualisieren. Wir haben zwei SQL-Bit-Typen: bit(n) und bit varying(n), wobei n eine positive ganze Zahl ist.

Daten des Typs bit müssen genau der Länge n entsprechen; der Versuch, kürzere oder längere Daten zu speichern, ist ein Fehler. Daten des Typs bit varying sind ein Typ mit variabler Länge und einer maximalen Länge von n; längere Zeichenketten werden abgelehnt. Das Schreiben von bit ohne Länge entspricht bit(1), und bit varying ohne Länge bedeutet keine Längenbeschränkung.

Textsuchtypen

Die Volltextsuche ist das Abrufen von Dokumenten aus einer Sammlung von Dokumenten in natürlicher Sprache, die einer Abfrage entsprechen.

PostgreSQL bietet zwei Datentypen zur Unterstützung der Volltextsuche:

UUID-Typ

Der Datentyp uuid wird verwendet, um universell eindeutige Bezeichner (UUID) gemäß RFC 4122, ISO/IEF 9834-8:2005 und verwandten Standards zu speichern. (Manche Systeme bezeichnen diesen Datentyp als globally unique identifier oder GUID.) Dieser Bezeichner ist ein 128-Bit-Bezeichner, der von einem Algorithmus erzeugt wird, wodurch es unmöglich ist, dass er mit Bezeichnern identisch ist, die auf andere Weise unter Modulen erzeugt werden, von denen bekannt ist, dass sie denselben Algorithmus verwenden. Daher bietet dieser Bezeichner für verteilte Systeme eine bessere Eindeutigkeitsgarantie als Sequenzen, da Sequenzen Eindeutigkeit nur innerhalb einer einzelnen Datenbank garantieren können.

UUIDs werden als Folge von kleingeschriebenen Hexadezimalziffern geschrieben, die durch Bindestriche in Gruppen unterteilt sind, und zwar eine Gruppe aus 8 Ziffern plus 3 Gruppen aus 4 Ziffern plus eine Gruppe aus 12 Ziffern, insgesamt 32 Ziffern, die 128 Bit darstellen. Ein Beispiel für eine solche Standard-UUID sieht wie folgt aus:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML-Typ

Der Datentyp xml kann zum Speichern von XML-Daten verwendet werden. Sein Vorteil gegenüber dem Speichern von XML-Daten im Typ text besteht darin, dass er Eingabewerte auf Wohlgeformtheit prüfen kann und außerdem Funktionen für die typsichere Prüfung dieser Werte unterstützt. Um diesen Datentyp zu verwenden, müssen Sie ihn bei der Kompilierung verwenden.configure --with-libxml。

xml kann wohlgeformte "Dokumente" speichern, die durch den XML-Standard definiert sind, sowie durch die vom XML-StandardXMLDecl? contentdefinierten "content"-Fragmente; grob gesagt bedeutet dies, dass content-Fragmente mehrere Elemente der obersten Ebene oder Zeichenknoten haben können. Der Ausdruck xml value IS DOCUMENT kann verwendet werden, um festzustellen, ob ein bestimmter xml-Wert ein vollständiges Dokument oder ein content-Fragment ist.

XML-Werte erstellen

Verwenden Sie die Funktion xmlparse:, um xml-Typwerte aus Zeichendaten zu erzeugen:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON-Typen

Der Datentyp json kann verwendet werden, um JSON-Daten (JavaScript Object Notation) zu speichern. Solche Daten können auch als Text gespeichert werden, aber der Datentyp json ist vorteilhafter, um zu prüfen, dass jeder gespeicherte Wert ein gültiger JSON-Wert ist.

Darüber hinaus gibt es zugehörige Funktionen zur Verarbeitung von json-Daten:

Array-Typen

PostgreSQL ermöglicht es, Felder als mehrdimensionale Arrays mit variabler Länge zu definieren.

Array-Typen können ein beliebiger Basistyp oder benutzerdefinierter Typ, Enum-Typ oder zusammengesetzter Typ sein.

Deklarieren von Arrays

Beim Erstellen einer Tabelle können Arrays wie folgt deklariert werden:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter ist ein eindimensionales Integer-Array, und schedule ist ein zweidimensionales Text-Array.

Wir können auch das Schlüsselwort "ARRAY" verwenden, wie unten gezeigt:

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

Werte einfügen

Um Werte einzufügen, verwenden Sie geschweifte Klammern {}, wobei die Elemente innerhalb von {} durch Kommas getrennt werden:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Zugriff auf Arrays

Jetzt können wir einige Abfragen auf dieser Tabelle ausführen.

Zuerst zeigen wir, wie man auf ein einzelnes Element eines Arrays zugreift. Diese Abfrage ruft die Namen der Mitarbeiter ab, deren Gehälter sich im zweiten Quartal geändert haben:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

Die Indexnummern eines Arrays werden in eckige Klammern geschrieben.

Arrays ändern

Wir können die Werte eines Arrays ändern:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

Oder verwenden Sie die ARRAY-Konstruktor-Syntax:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Suchen in Arrays

Um einen Wert in einem Array zu suchen, müssen Sie jeden Wert des Arrays überprüfen.

Zum Beispiel:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Alternativ können Sie die folgende Anweisung verwenden, um Zeilen zu finden, in denen alle Elementwerte im Array gleich 10000 sind:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Oder Sie können die Funktion generate_subscripts verwenden. Zum Beispiel:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Zusammengesetzte Typen

Ein zusammengesetzter Typ repräsentiert die Struktur einer Zeile oder eines Datensatzes; er ist tatsächlich nur eine Liste von Feldnamen und deren Datentypen. PostgreSQL erlaubt es, zusammengesetzte Typen auf dieselbe Weise wie einfache Datentypen zu verwenden. Beispielsweise kann ein Feld einer Tabelle als zusammengesetzter Typ deklariert werden.

Deklaration zusammengesetzter Typen

Nachfolgend finden Sie zwei einfache Beispiele für die Definition zusammengesetzter Typen:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

Die Syntax ähnelt CREATE TABLE, mit dem Unterschied, dass hier nur Feldnamen und Typen deklariert werden können.

Sobald die Typen definiert sind, können wir sie verwenden, um Tabellen zu erstellen:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Eingabe von Werten zusammengesetzter Typen

Um einen Wert eines zusammengesetzten Typs als Textkonstante zu schreiben, schließen Sie die Feldwerte in Klammern ein und trennen sie durch Kommas. Sie können jeden Feldwert in doppelte Anführungszeichen setzen; wenn der Wert selbst Kommas oder Klammern enthält, müssen Sie ihn in doppelte Anführungszeichen einschließen.

Das allgemeine Format einer Konstante eines zusammengesetzten Typs ist wie folgt:

'( val1 , val2 , ... )'

Ein Beispiel ist:

'("fuzzy dice",42,1.99)'

Zugriff auf zusammengesetzte Typen

Um auf ein Feld einer Spalte mit zusammengesetztem Typ zuzugreifen, schreiben wir einen Punkt und den Feldnamen, ganz ähnlich wie bei der Auswahl eines Felds aus einem Tabellennamen. Tatsächlich müssen wir, weil es so ähnlich wie die Auswahl eines Felds aus einem Tabellennamen ist, oft Klammern verwenden, um den Parser nicht zu verwirren. Zum Beispiel müssen Sie möglicherweise einige Unterfelder aus der Beispieltabelle on_hand auswählen, etwa so:

SELECT item.name FROM on_hand WHERE item.price > 9.99;

Dies wird nicht funktionieren, denn gemäß der SQL-Syntax wird item aus einem Tabellennamen ausgewählt, nicht aus einem Feldnamen. Sie müssen es so schreiben:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Oder wenn Sie auch den Tabellennamen verwenden müssen (zum Beispiel in einer Abfrage über mehrere Tabellen), dann schreiben Sie es so:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Jetzt wird das eingeklammerte Objekt korrekt als Verweis auf das Feld item geparst, und dann können Sie Unterfelder daraus auswählen.

Bereichstypen

Bereichsdatentypen repräsentieren einen bestimmten Wertebereich eines bestimmten Elementtyps.

Zum Beispiel könnte ein Zeitstempelbereich verwendet werden, um den Zeitraum darzustellen, während dem ein Besprechungsraum reserviert ist.

Die in PostgreSQL integrierten Bereichstypen sind:

• int4range — Bereich von integer

• int8range — Bereich von bigint

• numrange — Bereich von numeric

• tsrange — Bereich von timestamp ohne Zeitzone

• tstzrange — Bereich von timestamp mit Zeitzone

• daterange — Bereich von date

Darüber hinaus können Sie Ihre eigenen Bereichstypen definieren.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

Die Eingabe für Bereichswerte muss dem folgenden Format entsprechen:

(lower bound, upper bound)
(lower bound, upper bound]
[lower bound, upper bound)
[lower bound, upper bound]
empty

Runde Klammern oder eckige Klammern geben an, ob die untere und obere Grenze exklusiv oder inklusiv sind. Beachten Sie, dass das letzte Format leer ist und einen leeren Bereich darstellt (einen Bereich, der keine Werte enthält).

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

Objektbezeichner-Typen

PostgreSQL verwendet intern Objektbezeichner (OIDs) als Primärschlüssel für verschiedene Systemtabellen.

Gleichzeitig fügt das System benutzererstellten Tabellen kein OID-Systemfeld hinzu (es sei denn, WITH OIDS wird beim Erstellen der Tabelle angegeben oder der Konfigurationsparameter default_with_oids ist auf on gesetzt). Der Typ oid repräsentiert einen Objektbezeichner. Darüber hinaus hat oid mehrere Aliase: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig und regdictionary.

Pseudo-Typen

Das PostgreSQL-Typensystem enthält eine Reihe von Einträgen für spezielle Zwecke, die gemeinsam als Pseudo-Typen bezeichnet werden. Pseudo-Typen können nicht als Datentypen von Feldern verwendet werden, aber sie können verwendet werden, um den Argument- oder Ergebnistyp einer Funktion zu deklarieren. Pseudo-Typen sind nützlich, wenn eine Funktion nicht einfach einen bestimmten SQL-Datentyp annimmt und zurückgibt.

Die folgende Tabelle listet alle Pseudo-Typen auf:

Weitere Informationen finden Sie unter:PostgreSQL-Datentypen