PostgreSQL – Datentypen

In diesem Kapitel werden wir über die in PostgreSQL verwendeten Datentypen sprechen. Beim Erstellen einer Tabelle geben Sie für jede Spalte einen Datentyp an, d.h.,

Dies ermöglicht mehrere Vorteile –

• Konsistenz – Operationen gegen Spalten desselben Datentyps liefern konsistente Ergebnisse und sind in der Regel am schnellsten.

• Validierung – Die ordnungsgemäße Verwendung von Datentypen impliziert die Formatvalidierung von Daten und die Ablehnung von Daten außerhalb des Datentyps.

• Kompaktheit – Da eine Spalte nur einen einzigen Wertetyp speichern kann, wird sie kompakt gespeichert.

• Leistung – Die ordnungsgemäße Verwendung von Datentypen ermöglicht die effizienteste Speicherung von Daten. Die gespeicherten Werte können schnell verarbeitet werden, was die Leistung steigert.

PostgreSQL unterstützt eine große Anzahl von Datentypen. Darüber hinaus können Benutzer ihre eigenen benutzerdefinierten Datentypen mit dem SQL-Befehl CREATE TYPE erstellen. Es gibt verschiedene Kategorien von Datentypen in PostgreSQL. Sie werden im Folgenden beschrieben.

Numerische Typen

Numerische Typen bestehen aus Zwei-Byte-, Vier-Byte- und Acht-Byte-Ganzzahlen, Vier-Byte- und Acht-Byte-Gleitkommazahlen und Dezimalzahlen mit wählbarer Genauigkeit. In der folgenden Tabelle sind die verfügbaren Typen aufgeführt.

Name	Speichergröße	Beschreibung	Bereich
smallint	2 Bytes	small-Bereich integer	-32768 bis +32767
integer	4 bytes	typische Wahl für integer	-2147483648 bis +2147483647
bigint	8 Bytes	Großbereich-Ganzzahl	-9223372036854775808 bis 9223372036854775807
dezimal	variabel	benutzerdefinierte Genauigkeit,exakt	bis zu 131072 Stellen vor dem Dezimalpunkt; bis zu 16383 Stellen nach dem Dezimalpunkt
numerisch	variabel	benutzerdefinierte Genauigkeit, exakt	bis zu 131072 Stellen vor dem Dezimalpunkt; bis zu 16383 Stellen nach dem Dezimalpunkt
real	4 Bytes	variabel-genau, ungenau	6 Dezimalstellen genau
doppelt genau	8 Bytes	variabel-genau,ungenau	15 Dezimalstellen Genauigkeit
kleinseriell	2 Bytes	kleine autoincrementierende Ganzzahl	1 bis 32767
seriell	4 Bytes	autoinkrementierend Ganzzahl	1 bis 2147483647
bigserial	8 bytes	große autoincrementing integer	1 bis 9223372036854775807

Geldtypen

Der Geldtyp speichert einen Währungsbetrag mit einer festen Bruchgenauigkeit. Werte der Datentypen numeric, int und bigint können in Geld umgewandelt werden. Die Verwendung von Fließkommazahlen wird nicht empfohlen, um Geld zu verarbeiten, da es zu Rundungsfehlern kommen kann.

Name	Speichergröße	Beschreibung	Bereich
Geld	8 Bytes	Währungsbetrag	-92233720368547758.08 bis +92233720368547758.07

Zeichentypen

In der folgenden Tabelle sind die in PostgreSQL verfügbaren Allzweck-Zeichentypen aufgeführt.

S. Nr.	Name & Beschreibung
1	Zeichen variierend(n), varchar(n) variable Länge mit Limit
2	Zeichen(n), char(n) feste Länge, Leerzeichen aufgefüllt
3	Text variable unbegrenzte Länge

Binäre Datentypen

Der Datentyp bytea ermöglicht die Speicherung von binären Zeichenketten wie in der folgenden Tabelle angegeben.

Name	Speichergröße	Beschreibung
bytea	1 oder 4 Bytes plus die eigentliche binäre Zeichenfolge	variable-Länge Binärstring

Datums-/Zeittypen

PostgreSQL unterstützt einen vollständigen Satz von SQL Datums- und Zeittypen, wie in der folgenden Tabelle gezeigt. Die Datumsangaben werden nach dem Gregorianischen Kalender gezählt. Hier haben alle Typen eine Auflösung von 1 Mikrosekunde / 14 Ziffern mit Ausnahme des Datumstyps, dessen Auflösung ein Tag ist.

Name	Speichergröße	Beschreibung	Niedriger Wert	Hoher Wert
Zeitstempel	8 Bytes	beide Datum und Zeit (keine Zeitzone)	4713 BC	294276 AD
TIMESTAMPTZ	8 Bytes	beide Datum und Zeit, mit Zeitzone	4713 v. Chr.	294276 n. Chr.
Datum	4 Bytes	Datum (keine Uhrzeit)	4713 v. Chr.	5874897 n. Chr.
Zeit	8 Bytes	Tageszeit (kein Datum)	00:00:00	24:00:00
Uhrzeit mit Zeitzone	12 Bytes	nur Tageszeiten, mit Zeitzone	00:00:00+1459	24:00:00-1459
Intervall	12 Bytes	Zeitintervall	-178000000 Jahre	178000000 Jahre

Boolean Typ

PostgreSQL stellt den Standard SQL Typ Boolean zur Verfügung. Der boolesche Datentyp kann die Zustände wahr, falsch und einen dritten Zustand, unbekannt, haben, der durch den SQL-Nullwert dargestellt wird.

Name	Speichergröße	Beschreibung
Bolean	1 Byte	Zustand von wahr oder falsch

Aufzählungstyp

Aufzählungstypen (enum) sind Datentypen, die eine statische, geordnete Menge 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 verwendet, um eine statische, geordnete Menge von Werten zu speichern. Zum Beispiel Himmelsrichtungen, d.h. 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 Typen können nach ihrer Erstellung wie alle anderen Typen verwendet werden.

Geometrischer Typ

Geometrische Datentypen stellen zweidimensionale räumliche Objekte dar. Der grundlegendste Typ, der Punkt, bildet die Basis für alle anderen Typen.

Name	Speichergröße	Darstellung	Beschreibung
Punkt	16 Bytes	Punkt in einer Ebene	(x,y)
Linie	32 Bytes	Unendliche Linie (nicht vollständig implementiert)	((x1,y1),(x2,y2))
lseg	32 bytes	Finite line segment	((x1,y1),(x2,y2))
box	32 bytes	Rectangular box	((x1,y1),(x2,y2))
Pfad	16+16n Bytes	Geschlossener Pfad (ähnlich Polygon)	((x1,y1),…)
Pfad	16+16n Bytes	Offener Pfad
Polygon	40+16n	Polygon (ähnlich dem geschlossenen Pfad)	((x1,y1),…)
Kreis	24 Bytes	Kreis	<(x,y),r> (Mittelpunkt und Radius)

Netzwerkadresstyp

PostgreSQL bietet Datentypen zum Speichern von IPv4, IPv6 und MAC Adressen. Es ist besser, diese Typen anstelle von reinen Texttypen zu verwenden, um Netzwerkadressen zu speichern, da diese Typen Eingabefehlerprüfung und spezielle 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

Bit-String-Typen werden zum Speichern von Bitmasken verwendet. Sie sind entweder 0 oder 1. Es gibt zwei SQL-Bit-Typen: bit(n) und bit varying(n), wobei n eine positive ganze Zahl ist.

Text Search Type

Dieser Typ unterstützt die Volltextsuche, d.h. das Durchsuchen einer Sammlung von Dokumenten in natürlicher Sprache, um diejenigen zu finden, die am besten zu einer Anfrage passen. Hierfür gibt es zwei Datentypen –

S. Nr.	Name &Beschreibung
1	tsvector Dies ist eine sortierte Liste eindeutiger Wörter, die normalisiert wurden, um verschiedene Varianten desselben Wortes, die so genannten „Lexeme“, zusammenzufassen.
2	tsquery Dies speichert Lexeme, nach denen gesucht werden soll, und kombiniert sie unter Beachtung der Booleschen Operatoren & (AND), | (OR) und ! (NICHT). Um die Gruppierung der Operatoren zu erzwingen, können Klammern verwendet werden.

UUID-Typ

Eine UUID (Universally Unique Identifiers) wird als eine Folge von klein geschriebenen hexadezimalen Ziffern in mehreren Gruppen geschrieben, die durch Bindestriche voneinander getrennt sind, insbesondere eine Gruppe von acht Ziffern, gefolgt von drei Gruppen von vier Ziffern, gefolgt von einer Gruppe von 12 Ziffern, für insgesamt 32 Ziffern, 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. Um XML-Daten zu speichern, 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 json-Datentyp kann verwendet werden, um JSON-Daten (JavaScript Object Notation) zu speichern. Solche Daten können auch als Text gespeichert werden, aber der json-Datentyp hat den Vorteil, dass bei jedem gespeicherten Wert geprüft wird, ob es sich um einen gültigen JSON-Wert handelt. Es gibt auch entsprechende Unterstützungsfunktionen, die direkt für die Verarbeitung des JSON-Datentyps verwendet werden können, wie folgt.

Beispiel	Beispielergebnis
array_to_json(‚{{1,5},{99,100}}‘::int)	,]
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 von jedem eingebauten oder benutzerdefinierten Basistyp, Enum-Typ oder zusammengesetzten Typ erstellt werden.

Deklaration von Arrays

Der Array-Typ kann deklariert werden als

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer, scheme text);

oder durch Verwendung des Schlüsselworts „ARRAY“ als

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY, scheme text);

Einfügen von Werten

Array-Werte können als literale Konstante eingefügt werden, wobei die Elementwerte in geschweifte Klammern eingeschlossen und durch Kommas getrennt werden. Ein Beispiel ist unten dargestellt –

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 nachstehenden 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 > saving_per_quarter;

Ändern von Feldern

Ein Beispiel für das Ändern von Feldern ist nachstehend dargestellt.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';

oder mit der ARRAY-Ausdrucksyntax –

UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';

Durchsuchen von Arrays

Ein Beispiel für das Durchsuchen von Arrays ist im Folgenden dargestellt.

SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;

Wenn die Größe des Arrays bekannt ist, kann die oben angegebene Suchmethode verwendet werden. Andernfalls zeigt das folgende Beispiel, wie man sucht, 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, d.h.,

Deklaration von 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 bei der Erstellung von Tabellen wie folgt verwendet werden –

CREATE TABLE on_hand ( item inventory_item, count integer);

Zusammengesetzte Werteingabe

Zusammengesetzte Werte können als literale Konstante eingefügt werden, wobei die Feldwerte in Klammern eingeschlossen und durch Kommas getrennt werden. Nachstehend ein Beispiel: –

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

Dies gilt für das oben definierte inventory_item. Das Schlüsselwort ROW ist eigentlich optional, solange Sie mehr als ein Feld im Ausdruck haben.

Zugriff auf zusammengesetzte Typen

Um auf ein Feld einer zusammengesetzten Spalte zuzugreifen, verwenden Sie einen Punkt gefolgt vom Feldnamen, ähnlich wie bei der Auswahl eines Feldes aus einem Tabellennamen. Um beispielsweise einige Unterfelder aus unserer Beispieltabelle on_hand auszuwählen, würde die Abfrage wie folgt lauten: –

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

Sie können auch den Tabellennamen verwenden (z. B. in einer multitable Abfrage), z. B. –

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

Bereichstypen

Bereichstypen sind Datentypen, die einen Datenbereich verwenden. Bereichstypen können diskrete Bereiche sein (z.B. alle ganzzahligen Werte von 1 bis 10) oder kontinuierliche Bereiche (z.B., jeder Zeitpunkt zwischen 10:00 und 11:00 Uhr).

Die eingebauten

• int4range – Bereich von integer

• int8range – Bereich von bigint

• numrange – Bereich von numeric

• tsrange – Zeitstempelbereich ohne Zeitzone

• tstzrange – Zeitstempelbereich mit Zeitzone

• daterange – Datumsbereich

Benutzerdefinierte Bereichstypen können erstellt werden, um neue Arten von Bereichen verfügbar zu machen, wie z. B. IP-Adressbereiche, die den Typ inet als Basis verwenden, oder Float-Bereiche, die den Datentyp float als Basis verwenden.

Bereichstypen unterstützen einschließende und ausschließende Bereichsgrenzen unter Verwendung der Zeichen ( ) und ( ). Zum Beispiel steht ‚[4,9)‘ für alle ganzen Zahlen von einschließlich 4 bis einschließlich 9.

Objektbezeichner-Typen

Objektbezeichner (OIDs) werden von PostgreSQL intern als Primärschlüssel für verschiedene Systemtabellen verwendet. Wenn WITH OIDS angegeben ist 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 verschiedene Alias-Typen aufgeführt. Die OID-Aliastypen haben keine eigenen Operationen mit Ausnahme von spezialisierten Ein- und Ausgaberoutinen.

Name		Beschreibung	Wert Beispiel
oid	beliebig	numerisch Objektbezeichner	564182
regproc	pg_proc	Funktionsname	sum
regprocedure	pg_proc	Funktion mit Argumenttypen	sum(int4)
regoper	pg_operator	Operatorname	+
regoperator	pg_operator	Operator mit Argumenttypen	*(integer,integer) oder -(NONE,Ganzzahl)
regclass	pg_class	Beziehungsname	pg_type
regtype	pg_type	Datentypname	ganzzahlig
regconfig	pg_ts_config	Text-Suchkonfiguration	Englisch
regdictionary	pg_ts_dict	Text-Suchwörterbuch	simple

Pseudo-Typen

Das PostgreSQL-Typsystem enthält eine Reihe von Spezial-Zweckeinträge, die zusammen als Pseudo-Typen bezeichnet werden. Ein Pseudo-Typ kann nicht als Spalten-Datentyp verwendet werden, aber er kann verwendet werden, um den Argument- oder Ergebnistyp einer Funktion zu deklarieren.

Die folgende Tabelle listet die vorhandenen Pseudo-Typen auf.

S. Nr.	Name &Beschreibung
1	any Zeigt an, dass eine Funktion einen beliebigen Eingangsdatentyp akzeptiert.
2	irgendeinElement Zeigt an, dass eine Funktion jeden Datentyp akzeptiert.
3	irgendeinArray Zeigt an, dass eine Funktion jeden Array-Datentyp akzeptiert.
4	anynonarray Zeigt an, dass eine Funktion jeden Nicht-Array-Datentyp akzeptiert.
5	anyenum Zeigt an, dass eine Funktion jeden Enum-Datentyp akzeptiert.
6	anyrange Zeigt an, dass eine Funktion einen beliebigen Bereichsdatentyp akzeptiert.
7	cstring Zeigt an, dass eine Funktion einen null-terminierten C-String akzeptiert oder zurückgibt.
8	intern Zeigt 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 Fremddaten-Wrapper-Handler wird deklariert, um fdw_handler zurückzugeben.
11	record Identifiziert eine Funktion, die einen nicht spezifizierten Zeilentyp zurückgibt.
12	trigger Eine Triggerfunktion ist so deklariert, dass sie trigger zurückgibt.
13	void Zeigt an, dass eine Funktion keinen Wert zurückgibt.