PostgreSQL - Datentyp
In diesem Kapitel werden die in PostgreSQL verwendeten Datentypen erläutert. Beim Erstellen einer Tabelle geben Sie für jede Spalte einen Datentyp an, dh welche Art von Daten Sie in den Tabellenfeldern speichern möchten.
Dies ermöglicht mehrere Vorteile -
Consistency - Operationen für Spalten desselben Datentyps liefern konsistente Ergebnisse und sind normalerweise die schnellsten.
Validation - Die ordnungsgemäße Verwendung von Datentypen impliziert die Formatvalidierung von Daten und die Ablehnung von Daten außerhalb des Datentypbereichs.
Compactness - Da eine Spalte einen einzelnen Werttyp speichern kann, wird sie kompakt gespeichert.
Performance- Die ordnungsgemäße Verwendung von Datentypen ermöglicht die effizienteste Speicherung von Daten. Die gespeicherten Werte können schnell verarbeitet werden, was die Leistung verbessert.
PostgreSQL unterstützt eine Vielzahl von Datentypen. Außerdem können Benutzer mit dem SQL-Befehl CREATE TYPE ihren eigenen benutzerdefinierten Datentyp erstellen . In PostgreSQL gibt es verschiedene Kategorien von Datentypen. Sie werden unten diskutiert.
Numerische Typen
Numerische Typen bestehen aus Zwei-Byte-, Vier-Byte- und Acht-Byte-Ganzzahlen, Vier-Byte- und Acht-Byte-Gleitkommazahlen sowie Dezimalstellen mit auswählbarer Genauigkeit. In der folgenden Tabelle sind die verfügbaren Typen aufgeführt.
Name | Speichergröße | Beschreibung | Angebot |
---|---|---|---|
smallint | 2 Bytes | Ganzzahl mit kleiner Reichweite | -32768 bis +32767 |
ganze Zahl | 4 Bytes | typische Wahl für Ganzzahl | -2147483648 bis +2147483647 |
Bigint | 8 Bytes | Ganzzahl mit großer Reichweite | -9223372036854775808 bis 9223372036854775807 |
Dezimal | Variable | Benutzerdefinierte Genauigkeit, genau | bis zu 131072 Stellen vor dem Dezimalpunkt; bis zu 16383 Stellen nach dem Komma |
numerisch | Variable | Benutzerdefinierte Genauigkeit, genau | bis zu 131072 Stellen vor dem Dezimalpunkt; bis zu 16383 Stellen nach dem Komma |
echt | 4 Bytes | variable Genauigkeit, ungenau | Genauigkeit von 6 Dezimalstellen |
Doppelte Genauigkeit | 8 Bytes | variable Genauigkeit, ungenau | Genauigkeit von 15 Dezimalstellen |
smallserial | 2 Bytes | kleine automatisch inkrementierende Ganzzahl | 1 bis 32767 |
seriell | 4 Bytes | Autoincrementing Integer | 1 bis 2147483647 |
bigserial | 8 Bytes | große automatisch inkrementierende Ganzzahl | 1 bis 9223372036854775807 |
Geldtypen
Die Geldart speichert einen Währungsbetrag mit einer festen Bruchgenauigkeit. Werte der Datentypen numerisch, int und bigint können in Geld umgewandelt werden . Die Verwendung von Gleitkommazahlen wird nicht empfohlen, um mit Geld umzugehen, da Rundungsfehler auftreten können.
Name | Speichergröße | Beschreibung | Angebot |
---|---|---|---|
Geld | 8 Bytes | Währungsbetrag | -92233720368547758.08 bis +92233720368547758.07 |
Zeichentypen
In der folgenden Tabelle sind die in PostgreSQL verfügbaren allgemeinen Zeichentypen aufgeführt.
S. Nr. | Name & Beschreibung |
---|---|
1 | character varying(n), varchar(n) variable Länge mit Limit |
2 | character(n), char(n) feste Länge, blank gepolstert |
3 | text variable unbegrenzte Länge |
Binäre Datentypen
Der Datentyp bytea ermöglicht die Speicherung von Binärzeichenfolgen wie in der folgenden Tabelle angegeben.
Name | Speichergröße | Beschreibung |
---|---|---|
bytea | 1 oder 4 Bytes plus die eigentliche Binärzeichenfolge | Binärzeichenfolge mit variabler Länge |
Datums- / Zeittypen
PostgreSQL unterstützt einen vollständigen Satz von SQL-Datums- und Uhrzeittypen, wie in der folgenden Tabelle gezeigt. Die Daten werden nach dem Gregorianischen Kalender gezählt. Hier haben alle Typen eine Auflösung von1 microsecond / 14 digits außer date Typ, dessen Auflösung ist day.
Name | Speichergröße | Beschreibung | Niedriger Wert | Hochwertig |
---|---|---|---|---|
Zeitstempel [(p)] [ohne Zeitzone] | 8 Bytes | Datum und Uhrzeit (keine Zeitzone) | 4713 v | 294276 AD |
TIMESTAMPTZ | 8 Bytes | Datum und Uhrzeit mit Zeitzone | 4713 v | 294276 AD |
Datum | 4 Bytes | Datum (keine Tageszeit) | 4713 v | 5874897 AD |
Zeit [(p)] [ohne Zeitzone] | 8 Bytes | Tageszeit (kein Datum) | 00:00:00 | 24:00:00 |
Zeit [(p)] mit Zeitzone | 12 Bytes | Nur Tageszeiten mit Zeitzone | 00: 00: 00 + 1459 | 24: 00: 00-1459 |
Intervall [Felder] [(p)] | 12 Bytes | Zeitintervall | -178000000 Jahre | 178000000 Jahre |
Boolescher Typ
PostgreSQL bietet den Standard-SQL-Typ Boolean. Der boolesche Datentyp kann die Zustände true , false und einen dritten unbekannten Zustand haben , der durch den SQL-Nullwert dargestellt wird.
Name | Speichergröße | Beschreibung |
---|---|---|
Boolescher Wert | 1 Byte | Zustand von wahr oder falsch |
Aufzählungstyp
Aufzählungstypen (Aufzählungstypen) sind Datentypen, die einen statischen, geordneten Satz von Werten umfassen. Sie entsprechen den Aufzählungstypen, die in einer Reihe von Programmiersprachen unterstützt werden.
Im Gegensatz zu anderen Typen müssen Aufzählungstypen mit dem Befehl CREATE TYPE erstellt werden. Dieser Typ wird zum Speichern eines statischen, geordneten Satzes von Werten verwendet. Zum Beispiel Kompassrichtungen, dh NORD, SÜD, OST und WEST oder Wochentage wie unten gezeigt -
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
Aufgezählte Aufzählungen können wie alle anderen Typen verwendet werden.
Geometrischer Typ
Geometrische Datentypen repräsentieren zweidimensionale räumliche Objekte. Der grundlegendste Typ, der Punkt, bildet die Grundlage für alle anderen Typen.
Name | Speichergröße | Darstellung | Beschreibung |
---|---|---|---|
Punkt | 16 Bytes | Zeigen Sie auf ein Flugzeug | (x, y) |
Linie | 32 Bytes | Unendliche Leitung (nicht vollständig implementiert) | ((x1, y1), (x2, y2)) |
lseg | 32 Bytes | Endliches Liniensegment | ((x1, y1), (x2, y2)) |
Box | 32 Bytes | Rechteckige Box | ((x1, y1), (x2, y2)) |
Pfad | 16 + 16n Bytes | Geschlossener Pfad (ähnlich wie Polygon) | ((x1, y1), ...) |
Pfad | 16 + 16n Bytes | Pfad öffnen | [(x1, y1), ...] |
Polygon | 40 + 16n | Polygon (ähnlich wie geschlossener Pfad) | ((x1, y1), ...) |
Kreis | 24 Bytes | Kreis | <(x, y), r> (Mittelpunkt und Radius) |
Netzwerkadressentyp
PostgreSQL bietet Datentypen zum Speichern von IPv4-, IPv6- und MAC-Adressen. Es ist besser, diese Typen anstelle von Nur-Text-Typen zum Speichern von Netzwerkadressen zu verwenden, da diese Typen eine Eingabefehlerprüfung sowie spezialisierte Operatoren und Funktionen bieten.
Name | Speichergröße | Beschreibung |
---|---|---|
cidr | 7 oder 19 Bytes | IPv4- und IPv6-Netzwerke |
inet | 7 oder 19 Bytes | IPv4- und IPv6-Hosts und -Netzwerke |
macaddr | 6 Bytes | MAC-Adressen |
Bit-String-Typ
Bitstringtypen werden zum Speichern von Bitmasken verwendet. Sie sind entweder 0 oder 1. Es gibt zwei SQL-Bittypen:bit(n) und bit varying(n), wobei n eine positive ganze Zahl ist.
Textsuchtyp
Dieser Typ unterstützt die Volltextsuche, bei der eine Sammlung von Dokumenten in natürlicher Sprache durchsucht wird, um diejenigen zu finden, die am besten zu einer Abfrage passen. Hierfür gibt es zwei Datentypen:
S. Nr. | Name & Beschreibung |
---|---|
1 | tsvector Dies ist eine sortierte Liste verschiedener Wörter, die normalisiert wurden, um verschiedene Varianten desselben Wortes zusammenzuführen, die als "Lexeme" bezeichnet werden. |
2 | tsquery Dies speichert Lexeme, nach denen gesucht werden soll, und kombiniert sie unter Berücksichtigung der Booleschen Operatoren & (AND), | (Oder und ! (NICHT). Klammern können verwendet werden, um die Gruppierung der Operatoren zu erzwingen. |
UUID-Typ
Eine UUID (Universally Unique Identifiers) wird als Folge von hexadezimalen Kleinbuchstaben in mehreren durch Bindestriche getrennten Gruppen geschrieben, insbesondere einer Gruppe von acht Ziffern, gefolgt von drei Gruppen mit vier Ziffern, gefolgt von einer Gruppe von 12 Ziffern Insgesamt 32 Stellen, die die 128 Bits darstellen.
Ein Beispiel für eine UUID ist - 550e8400-e29b-41d4-a716-446655440000
XML-Typ
Der XML-Datentyp kann zum Speichern von XML-Daten verwendet werden. Zum Speichern von XML-Daten müssen Sie zunächst XML-Werte mit der Funktion xmlparse wie folgt erstellen:
XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
<topics>...</topics>
</tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')
JSON-Typ
Der Datentyp json kann zum Speichern von JSON-Daten (JavaScript Object Notation) verwendet werden. Solche Daten können auch als Text gespeichert werden , aber der json- Datentyp hat den Vorteil, dass überprüft wird, ob jeder gespeicherte Wert ein gültiger JSON-Wert ist. Es stehen auch verwandte Unterstützungsfunktionen zur Verfügung, mit denen der JSON-Datentyp wie folgt direkt verarbeitet werden kann.
Beispiel | Beispiel Ergebnis |
---|---|
array_to_json ('{{1,5}, {99,100}}' :: int []) | [[1,5], [99,100]] |
row_to_json (row (1, 'foo')) | {"f1": 1, "f2": "foo"} |
Array-Typ
PostgreSQL bietet die Möglichkeit, eine Spalte einer Tabelle als mehrdimensionales Array variabler Länge zu definieren. Es können Arrays eines beliebigen integrierten oder benutzerdefinierten Basistyps, Aufzählungstyps oder zusammengesetzten Typs erstellt werden.
Deklaration von Arrays
Der Array-Typ kann als deklariert werden
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer[],
scheme text[][]
);
oder mit dem Schlüsselwort "ARRAY" als
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer ARRAY[4],
scheme text[][]
);
Werte einfügen
Array-Werte können als Literalkonstante eingefügt werden, wobei die Elementwerte in geschweiften Klammern eingeschlossen und durch Kommas getrennt werden. Ein Beispiel ist unten gezeigt -
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);
Zugriff auf Arrays
Ein Beispiel für den Zugriff auf Arrays ist unten dargestellt. Mit dem folgenden Befehl werden die Personen ausgewählt, deren Ersparnisse im zweiten Quartal höher sind als im vierten Quartal.
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];
Arrays ändern
Ein Beispiel für das Ändern von Arrays ist unten dargestellt.
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';
oder mit der ARRAY-Ausdruckssyntax -
UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';
Arrays suchen
Ein Beispiel für die Suche nach Arrays ist unten dargestellt.
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;
Wenn die Größe des Arrays bekannt ist, kann die oben angegebene Suchmethode verwendet werden. Andernfalls zeigt das folgende Beispiel, wie gesucht wird, wenn die Größe nicht bekannt ist.
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);
Zusammengesetzte Typen
Dieser Typ repräsentiert eine Liste von Feldnamen und deren Datentypen, dh die Struktur einer Zeile oder eines Datensatzes einer Tabelle.
Erklärung der zusammengesetzten Typen
Das folgende Beispiel zeigt, wie ein zusammengesetzter Typ deklariert wird
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
Dieser Datentyp kann in den folgenden Tabellen zum Erstellen verwendet werden:
CREATE TABLE on_hand (
item inventory_item,
count integer
);
Composite Value Input
Zusammengesetzte Werte können als Literalkonstante eingefügt werden, wobei die Feldwerte in Klammern eingeschlossen und durch Kommas getrennt werden. Ein Beispiel ist unten gezeigt -
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
Dies gilt für das oben definierte Inventarelement . Das Schlüsselwort ROW ist tatsächlich optional, solange der Ausdruck mehr als ein Feld enthält.
Zugriff auf zusammengesetzte Typen
Verwenden Sie für den Zugriff auf ein Feld einer zusammengesetzten Spalte einen Punkt gefolgt vom Feldnamen, ähnlich wie beim Auswählen eines Felds aus einem Tabellennamen. Um beispielsweise einige Unterfelder aus unserer Beispieltabelle on_hand auszuwählen, lautet die Abfrage wie folgt:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
Sie können auch den Tabellennamen verwenden (z. B. in einer multitablen Abfrage).
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
Bereichstypen
Bereichstypen stellen Datentypen dar, die einen Datenbereich verwenden. Der Bereichstyp kann diskrete Bereiche (z. B. alle ganzzahligen Werte 1 bis 10) oder kontinuierliche Bereiche (z. B. ein beliebiger Zeitpunkt zwischen 10:00 Uhr und 11:00 Uhr) sein.
Die verfügbaren integrierten Bereichstypen umfassen die folgenden Bereiche:
int4range - Bereich der ganzen Zahl
int8range - Reichweite von Bigint
numrange - Bereich der numerischen
tsrange - Bereich des Zeitstempels ohne Zeitzone
tstzrange - Bereich des Zeitstempels mit Zeitzone
daterange - Datumsbereich
Benutzerdefinierte Bereichstypen können erstellt werden, um neue Arten von Bereichen verfügbar zu machen, z. B. IP-Adressbereiche, die den Inet-Typ als Basis verwenden, oder Float-Bereiche, die den Float-Datentyp als Basis verwenden.
Bereichstypen unterstützen inklusive und exklusive Bereichsgrenzen mit den Zeichen [] bzw. (). Zum Beispiel repräsentiert '[4,9)' alle ganzen Zahlen, beginnend mit und einschließlich 4 bis einschließlich 9.
Objektkennungstypen
Objektkennungen (OIDs) werden von PostgreSQL intern als Primärschlüssel für verschiedene Systemtabellen verwendet. Wenn WITH OIDS angegeben oder die Konfigurationsvariable default_with_oids aktiviert ist, werden in solchen Fällen OIDs zu vom Benutzer erstellten Tabellen hinzugefügt. In der folgenden Tabelle sind mehrere Alias-Typen aufgeführt. Die OID-Alias-Typen haben außer speziellen Eingabe- und Ausgaberoutinen keine eigenen Operationen.
Name | Verweise | Beschreibung | Wertebeispiel |
---|---|---|---|
oid | irgendein | numerische Objektkennung | 564182 |
regproc | pg_proc | Funktionsname | Summe |
regprocedure | pg_proc | Funktion mit Argumenttypen | Summe (int4) |
Regoper | pg_operator | Name des Bedieners | + |
Regoperator | pg_operator | Operator mit Argumenttypen | * (Ganzzahl, Ganzzahl) oder - (KEINE, Ganzzahl) |
Regclass | pg_class | Beziehungsname | pg_type |
regtype | pg_type | Datentypname | ganze Zahl |
regconfig | pg_ts_config | Konfiguration der Textsuche | Englisch |
regdictionary | pg_ts_dict | Textsuchwörterbuch | einfach |
Pseudo-Typen
Das PostgreSQL-Typsystem enthält eine Reihe von Spezialeinträgen, die zusammen als Pseudotyp bezeichnet werden. Ein Pseudotyp kann nicht als Spaltendatentyp verwendet werden, aber er kann verwendet werden, um das Argument oder den Ergebnistyp einer Funktion zu deklarieren.
In der folgenden Tabelle sind die vorhandenen Pseudotypen aufgeführt.
S. Nr. | Name & Beschreibung |
---|---|
1 | any Gibt an, dass eine Funktion einen beliebigen Eingabedatentyp akzeptiert. |
2 | anyelement Gibt an, dass eine Funktion einen beliebigen Datentyp akzeptiert. |
3 | anyarray Gibt an, dass eine Funktion einen beliebigen Array-Datentyp akzeptiert. |
4 | anynonarray Gibt an, dass eine Funktion einen beliebigen Nicht-Array-Datentyp akzeptiert. |
5 | anyenum Gibt an, dass eine Funktion einen beliebigen Aufzählungsdatentyp akzeptiert. |
6 | anyrange Gibt an, dass eine Funktion einen beliebigen Bereichsdatentyp akzeptiert. |
7 | cstring Gibt an, dass eine Funktion eine nullterminierte C-Zeichenfolge akzeptiert oder zurückgibt. |
8 | internal Gibt an, dass eine Funktion einen serverinternen Datentyp akzeptiert oder zurückgibt. |
9 | language_handler Ein prozeduraler Sprachaufruf-Handler wird deklariert, um language_handler zurückzugeben. |
10 | fdw_handler Ein Wrapper-Handler für Fremddaten wird deklariert, um fdw_handler zurückzugeben. |
11 | record Identifiziert eine Funktion, die einen nicht angegebenen Zeilentyp zurückgibt. |
12 | trigger Eine Triggerfunktion wird deklariert, um den Trigger zurückzugeben. |
13 | void Gibt an, dass eine Funktion keinen Wert zurückgibt. |