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.