Datentypen

SQL-Datentypen und ihre
Besonderheiten
Holger Jakobs – [email protected], [email protected]
2008-09-05
Inhaltsverzeichnis
1 Grundlegendes und Datentypen
1.1 Oracle-Datentypen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 Systemdefinierte Werte . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
1
5
2 Besonderheiten/Ergänzungen
2.1 Besonderheiten von Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Besonderheiten von PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . .
5
5
7
Originaldokumentation zu Datentypen finden Sie im b.i.b. Bergisch Gladbach an diesen
Stellen:
Oracle: https://www.bg.bib.de/Bibliothek/oracle/server.111/b28318/datatype.htm
PostgreSQL: http://www.bg.bib.de/Bibliothek/postgresql/datatype.html
1 Grundlegendes und Datentypen
Im SQL-Standard sind diverse Datentypen festgelegt, die von den einzelnen Datenbanken leider nicht immer wie gefordert implementiert werden. Hier eine Übersicht über die
Standard-Datentypen, ihre Umsetzung in den Datenbanken PostgreSQL und Oracle sowie
über einige Erweiterungen der Datenbanken über den Standard hinaus.
1.1 Oracle-Datentypen
Oracle kennt beispielsweise nur einen einzigen numerischen Datentypen namens NUMBER,
bei dem man Größe und ggf. Nachkommastellen (Genauigkeit) angeben kann. Alle Zahlen
scheinen im Dezimalformat gespeichert zu werden. PostgreSQL dagegen kennt 2-Byteund 4-Byte-Integerzahlen sowie Fließkommazahlen in binärer Darstellung und auch genaue
gebrochene Dezimalzahlen, letztere sogar mit bis zu 1000 Stellen – bei Oracle sind es nur
38.
1
1.1 Oracle-Datentypen
1 GRUNDLEGENDES UND DATENTYPEN
Bei den Datum/Zeit-Werten sieht es ähnlich aus. Bei Oracle enthält der Datentyp DATE – entgegen dem ANSI-Standard – bereits eine Uhrzeit. PostgreSQL hat sowohl einen
standardgemäßen (reinen) Datumstypen als auch einen reinen Zeittypen und kombinierte
Typen (Zeitstempel) mit einer Auflösung von 1 Mikrosekunde. Oracle kennt über seinen
nicht standardgemäßen Datentypen DATE hinaus noch TIMESTAMP, aber keinen reinen Zeittypen und keinen reinen Datumstypen.
Zitat aus einer Dokumentation: Oracle is very particular about using date literals in
SQL. The proper date format is ’dd-mmm-yy’, where dd is a numeric day, mmm is a three
character month name, yy is two digit year. Some versions of Oracle give very strange
results or failures if date values are not in this format.
Im b.i.b. sind native language“-Einstellungen wie folgt über Umgebungsvariablen vor”
genommen worden:
NLS_DATE_FORMAT=YYYY-MM-DD
NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH24:MI:SSXFF"
NLS_LANG=German_Germany.UTF8
Daher werden das ISO-Format für Datumswerte und der UTF8-Zeichensatz für Zeichenketten verwendet. Es können also (zumindest unter Linux)auch Zeichenketten in nichtlateinischen Schriften gespeichert werden (Kyrillisch, Griechisch, Hebräisch, Arabisch, Japanisch, Chinesisch usw.). Bei PostgreSQL ist das ISO-Datumsformat Standard, und die
Speicherung von Zeichenketten hängt von der Angabe bei der Erzeugung der einzelnen Datenbank ab. Man kann es in der Spalte ENCODING sehen, wenn man im Frontend psql das
Kommando \l zum Auflisten der Datenbanken verwendet – bei uns ist einheitlich UTF8.
Damit die Anzeige auch im Oracle SQL-Developer standardgemäß ist und das Laden von Daten aus Textdateien funktioniert, ist es notwendig, im Menü Tools/Preferences die in Abbildung 1 gezeigten Einstellungen vorzunehmen.
Um bei der Vereinsdatenbank herauszubekommen, wie viele
Tage die eingetragenen Strafen alt sind, muss es im StandardSQL heißen:
select zahlnr, current_date - datum as tage
from strafen order by zahlnr;
In Oracle dagegen muss man eine dieser Varianten schreiben,
um die Zeitangabe aus dem aktuellen Datum CURRENT_DATE zu
unterdrücken und jeweils ein reines Datum zu erhalten, mit dem dann gerechnet werden
kann.
select zahlnr, to_date (to_char (CURRENT_DATE, 'yyyy-mm-dd')) - datum as tage
from sp_strafen order by zahlnr;
select zahlnr, trunc (CURRENT_DATE) - datum as tage
from sp_strafen order by zahlnr;
2
2 BESONDERHEITEN/ERGÄNZUNGEN
1.2 Systemdefinierte Werte
Abbildung 1: Format-Einstellungen im Oracle SQL-Developer
Diese Anweisungen setzen voraus, dass in der Spalte datum als Uhrzeit immer 00:00 gespeichert ist. Ansonsten müsste man die bei CURRENT_DATE gezeigte Verarbeitung auch bei
der Spalte vornehmen, damit immer ganze Zahlen herauskommen.
1.2 Systemdefinierte Werte
Es gibt für die Datum-/Zeit-Datentypen die systemdefinierten Werte CURRENT_DATE, CURRENT_TIME und CURRENT_TIMESTAMP. PostgreSQL kennt diese alle, Oracle nur das proprietäre SYSDATE, das zu seinem eigenen DATE-Format passt. Ähnlich gibt es den systemdefinierten Wert CURRENT_USER, den den Namen des ausführenden Benutzers zurückliefert.
Dieser heißt bei Oracle einfach USER, PostgreSQL hält sich an den Standard.
2 Besonderheiten/Ergänzungen
Besonderheiten eines einzelnen Datenbanksystems sind immer sehr kritisch zu sehen. So
schön und praktisch sie einerseits sein mögen, so störend sind sie, sobald man – aus welchem Grund auch immer – eine Datenbank auf ein anderes Datenbank-Management-System
transferieren möchte. Es ist daher immer von Vorteil, sich an im ANSI-Standard Vorgege-
3
2 BESONDERHEITEN/ERGÄNZUNGEN
ANSI SQL
INTEGER
SMALLINT
Die
Standard-Ganzzahltypen sind vorzeichenbehaftet. INTEGER
muss größer oder genauso groß sein wie
SMALLINT, die Größe
an sich ist implementationsabhängig.
Oracle
PostgreSQL
INTEGER
INTEGER
SMALLINT
SMALLINT
Oracle setzt die ANSI-Da- INTEGER wird in einen 32tentypen für ganze Zahlen bit-Integer-Datentypen umINTEGER und SMALLINT in gesetzt, SMALLINT in einen
einen Typen für 38-stellige 16-bit-Integer-Datentypen.
Dezimalzahlen um, d. h. bei- Wahlweise kann man auch
de Typen sind bei Oracle int4 und int2 schreiben,
identisch und brauchen sehr was dann aber mit keiner
viel Platz. Bei der Anzeige anderen Datenbank mehr
der Datendefinition erscheint kompatibel ist.
NUMBER(38). Möchte man
weniger Platz verwenden, so
muss man NUMBER(n ) angeben, was dann aber mit keiner anderen Datenbank mehr
kompatibel ist.
Tabelle 1: ganzzahlige numerische Datentypen (exakt)
ANSI SQL
DECIMAL(p,s )
NUMERIC(p,s )
Bei DECIMAL und NUMERIC werden Länge
und Anzahl Nachkommastellen angegeben.
Die Maximalwerte sind
implementationsabhängig. Verwendung
insbesondere
für
Geldbeträge in C.
Oracle
DECIMAL(p,s )
NUMERIC(p,s )
DECIMAL (p,s ) und NUMERIC (p,s ) werden zu NUMBER (p,s ), wobei p maximal 38 sein darf.
PostgreSQL
DECIMAL(p,s )
NUMERIC(p,s )
DECIMAL (p,s ) und NUMERIC (p,s ) werden zu NUMERIC (p,s ), wobei p maximal 1000 sein darf.
Tabelle 2: gebrochene, exakte numerische Datentypen
4
2 BESONDERHEITEN/ERGÄNZUNGEN
ANSI SQL
FLOAT(n ),
REAL,
DOUBLE PRECISION
Diese Datentypen geben Fließkommazahlen
an, die im Binärformat
gespeichert
werden.
Bei FLOAT kann man
die gewünschte Genauigkeit in Dezimalstellen angeben. DOUBLE
PRECISION ist genauer
als REAL, wobei die
Genauigkeit
implementationsabhängig
ist.
Oracle
FLOAT(n ), REAL, DOUBLE
PRECISION
Die Umsetzung dieses Datentyps ist nicht recht durchschaubar float(3) gibt nur
1 signifikante Stelle wieder,
float(7) und float(8) 3
und float(10) 4 signifikante Dezimalstellen. Bei der
Anzeige der Datendefinition
erscheinen NUMBER(3,129),
NUMBER(7,129),
NUMBER(8,129)
und
NUMBER(10,129).
Bei
REAL
wird
NUMBER(63,129) angezeigt,
bei
DOUBLE PRECISION
NUMBER(126,129) .
PostgreSQL
FLOAT(n ), REAL, DOUBLE
PRECISION
Bei Werten von n bis 6 werden 4-Byte-Fließkommazahlen verwendet, bei Werten ab
7 solche mit 8 Byte. Ohne
Längenangabe wird bei allen
dreien eine 8-Byte-Fließkommazahl verwendet.
Tabelle 3: gebrochene, angenäherte numerische Datentypen
ANSI SQL
BOOL.
Im
SQL3-Standard
wird ein Datentyp BOOL
vorgeschlagen.
Oracle
PostgreSQL
—
BOOL
Nicht enthalten. Man kann Der Datentyp entspricht dem
evtl. ein einzelnes Zeichen SQL3-Vorschlag.
mit Einschränkung auf zwei
Werte (mittels CHECK) verwenden. Logische Operatoren (AND, OR, NOT) kann man
darauf aber nicht anwenden.
Tabelle 4: logischer Datentyp
5
2 BESONDERHEITEN/ERGÄNZUNGEN
ANSI SQL
DATE,
TIME,
TIMESTAMP, INTERVAL
DATE ist ein reines Datum (ohne Zeit), TIME
ist eine reine Zeitangabe (ohne Datum)
mit (mindestens) Sekundenauflösung. TIME
und TIMESTAMP können
den Zusatz WITH TIME
ZONE tragen, d. h. eine
Zeitzoneninformation
beinhalten.
Oracle
DATE (mit Zeit), TIMESTAMP
PostgreSQL
DATE, TIME, TIMESTAMP,
INTERVAL
Es gibt in DATE, was Da- Es gibt alle genannten Tytum und zusätzlich die Zeit pen einschließich derer mit
(mit Sekundenauflösung) ent- Zeitzonen, wobei die Auflöhält. Das führt zu Problemen, sung der eine Uhrzeit enthalwenn man vom System das tenden Typen 1 Mikrosekunaktuelle Datum in ein Feld de ist. Das Datumsformat ist
eintragen und anschließend ISO-8601.
zwei Wert vom selben Tag
vergleichen lässt. Der Vergleich geht schief, weil sich
die Uhrzeit unterscheidet, obwohl das eigentliche Datum
gleich ist. Zeitzonen werden
nicht verwaltet. Das Datenformat des Tools TOra ist
ISO-8601, Oracle selbst verwendet aber noch das früher in den USA gebräuchliche Format dd-MMM-YY, sofern man nicht – wie im b.i.b.
geschehen – ein anderes Format einstellt. Bei uns ist ISO8601 eingestellt.
Es gibt zusätzlich einen standardkonformen TIMESTAMPTyp, der auch Sekundenbruchteile speichern kann und
mit dem Zeitzonenangaben
möglich sind.
Tabelle 5: Datum- und Zeit-Typen
6
2 BESONDERHEITEN/ERGÄNZUNGEN
ANSI SQL
Oracle
BIT,
BIT(n ), —
VARBIT(n )
Die
BIT-Datentypen Nicht enthalten.
sind
unstrukturierte
Bitketten fester bzw.
variabler Länge. Hier
können binäre Daten abgelegt werden.
Verwendung ist recht
selten.
2.1 Besonderheiten von Oracle
PostgreSQL
BIT(n ), VARBIT(n ) bzw.
BIT VARYING(n )
Der Datentyp entspricht dem
ANSI-Datentypen.
Tabelle 6: Bit-Datentyp
benes zu halten, evtl. sogar nur an Dinge, die außerdem von den gängigen Datenbanken
unterstützt werden. Leider wird die Anzahl Features dann recht klein, so dass man einen
gangbaren Kompromiss finden muss.
2.1 Besonderheiten von Oracle
Bei Oracle gibt es ein zusätzliches Attribut in allen Tabellen, die ROWID. Sie setzt sich aus
mehreren Teilen zusammen, die jeder für sich numerisch sind, aber durch Punkte getrennt
werden.
Es gibt raw-Datenfelder, die in ihrer long Variante bis zu 2 GB an Daten aufnehmen
können. Large Objects können bis zu 4GB umfassen. Noch größere Datenmengen kann man
im Dateisystem ablegen und in einem Attribut vom Type bfile ablegen“. Pro Tabelle
”
kann es nur ein long- oder long raw-Attribut geben, aber mehrere bfile-Attribute.
Für die Konversion der einzelnen Datentypen ineinander gibt es – über die impliziten Typkonvertierungen hinaus – diverse Konvertierungsfunktionen, z. B. to_char, hex_to_raw
usw. Ähnlich kann man mit Funktionen weitergehende Datumsarithmetik betreiben und
alle möglichen mathematischen Funktionen ausführen. Bitte die Funktionen in der OracleDoku nachschauen, damit Sie sich einen Überblick verschaffen!
Oracle-Datenbanken können auf vielfältige Weise optimiert werden. Der Einfluss auf die
physikalische Struktur ist enorm, verlangt aber auch umfangreiches Spezialwissen.
2.2 Besonderheiten von PostgreSQL
Bei PostgreSQL sind die Tabellen auch Klassen. Man kann neue Klassen von bestehenden
ableiten. Daher ist PostgreSQL eine objektrelationale Datenbank und unterstützt sehr gut
die Spezialisierung (vgl. UML und Software Engineering).
Für die Konvertierung von Datentypen ineinander über die impliziten Konversionen
hinaus gibt es Konvertierungsoperatoren, die mit einem doppelten Doppelpunkt eingeleitet
7
2.2 Besonderheiten von PostgreSQL
ANSI SQL
CHAR,
CHAR(n ),
VARCHAR(n ),
NCHAR,
NCHAR(n ),
NVARCHAR(n )
(und
gleichartige Schreibvarianten)
CHAR ist ein einzelnes
Zeichen,
CHAR(n )
eine Zeichenkette der
Länge n (geeignet für
Pascal und COBOL,
immer mit Leerzeichen
auf volle Länge aufgefüllt), VARCHAR(n )
ist eine variabel lange Zeichenkette der
maximalen Länge n
(geeignet für C/C++,
Java und die gängigen
Scriptsprachen).
Die
Varianten mit N davor
sind Zeichenketten mit
Unterstützung für landesspezifische Zeichen
(national
character
sets). Diese sind dann
wichtig, wenn man landesspezifisch sortieren
will, z. B. ü=u und ß=ss
bei der Sortierung nach
DIN.
2 BESONDERHEITEN/ERGÄNZUNGEN
Oracle
PostgreSQL
CHAR,
CHAR(n ), CHAR,
CHAR(n ),
VARCHAR2(n ),
NCHAR, VARCHAR(n ), TEXT
NCHAR(n ), NVARCHAR2(n )
Die Typen VARCHAR und
VARCHAR2 sind synonym,
aber es wird von Oracle zur
Verwendung von VARCHAR2
geraten. Die maximale Länge für CHAR beträgt 2000
Zeichen, für VARCHAR(2)
4000 Zeichen. Über nationale
Zeichensätze sind Sortierreihenfolgen beeinflussbar,
auch Multibyte-Zeichensätze
(Unicode) sind verwendbar,
dann sollte man NCHAR und
NVARCHAR bzw. NVARCHAR21
verwenden, die UTF-8Codierung2 ermöglichen.
Es werden diverse singleByteund
multi-ByteZeichensätze
mit
ihren
Sortier- und anderen Regeln
unterstützt. Die Multi-ByteUnterstützung darf dafür
beim Compilieren nicht
abgewählt worden sein.
Der Datentyp TEXT braucht
keine bestimmte Längenangabe und erlaubt bis zu 2 GB,
ist aber proprietär. Die Länge
der anderen Zeichenketten ist
nicht so eng begrenzt wie bei
Oracle.
Besonders praktisch ist er
dann, wenn man Dateien beliebigen Formats (z. B. Bilder) in der Datenbank ablegen
möchte. Diese codiert man
dann mit MIME (Kommando
mimencode) und legt die Zeichenkette in der Spalte ab.
Tabelle 7: Zeichenketten-Datentypen
8
2 BESONDERHEITEN/ERGÄNZUNGEN
2.2 Besonderheiten von PostgreSQL
werden. Wahlweise kann auch die Schreibweise mit CAST verwendet werden. Bitte schlagen
Sie die Konversionsmöglichkeiten und die Funktionen in der PostgreSQL-Doku nach, damit
Sie sich einen Überblick verschaffen.
Große Objekte können mit speziellen Funktionen in der Datenbank abgelegt werden.
Texte, die nicht in varchar-, sondern in text-Attributen abgelegt werden, haben keine
direkte Längenbeschränkung; dafür ist dieser Datentyp PostgreSQL-spezifisch.
Es gibt spezielle Datentypen für Polygonzüge, IPv4- und IPv6-Adressen und MACAdressen. Auch kann man in einem einzelnen Feld Arrays ablegen, was aber im Konflikt
mit dem streng relationalen Modell steht und daher nur in Ausnahmefällen Verwendung
finden sollte.
PostgreSQL erlaubt über die Definition von Indexen hinaus fast keinen Einfluss auf
die physische Datenorganisation, wohl aber kann über viele Parameter das Verhalten des
Servers optimiert werden.
$Id: datentypen.tex,v 1.3 2008-04-17 09:09:14 hj Exp $
9