Seit mehr als 20 Jahren arbeite ich professionell mit Microsoft SQL Server und ich habe das Produkt lieben gelernt. Mein ganzes Schaffen drehte sich nur um dieses Produkt. In den letzten Jahren wurde Postgres immer populärer. Viele internationale Kollegen schauen über den Tellerrand und entdecken vielfältige Möglichkeiten in Postgres, die auch dem SQL Server gut stehen würden. CREATE TABLE ist eine der Möglichkeiten, die ich in PostgreSQL besser gelöst sehe, als im SQL Server.
Inhaltsverzeichnis
Neue Tabelle erstellen – CREATE TABLE
Microsoft SQL Server
In Microsoft SQL Server gibt es leider nicht viele Möglichkeiten – sie erstrecken sich auf zwei generelle Befehle:
CREATE TABLE dbo.test
(
ID INT NOT NULL,
C1 CHAR(100) NOT NULL,
CONSTRAINT pk_test PRIMARY KEY CLUSTERED (ID)
);
GO
Sobald eine Tabelle erstellt wurde, kann sie nicht noch einmal erstellt werden (logisch!) und sofern sie existiert, wird es kompliziert.
IF OBJECT_ID(N'dbo.test', N'U') IS NULL
CREATE TABLE dbo.test
(
ID INT NOT NULL,
C1 CHAR(100) NOT NULL,
CONSTRAINT pk_test PRIMARY KEY CLUSTERED (ID)
);
GO
Zunächst muss überprüft werden, ob das Objekt existiert. Falls nicht, wird die Tabelle neu erstellt.
PostgreSQL
In PostgreSQL gestaltet sich das Erstellen einer Tabelle deutlich einfacher.
CREATE TABLE IF NOT EXISTS "dbo"."test"
(
ID INT NOT NULL,
C1 CHAR(100) NOT NULL,
CONSTRAINT pk_test PRIMARY KEY (ID)
);
Statt eines Fehlers erhalten wir eine Information; der Batch wird also NICHT abgebrochen.
Kopie einer Tabelle erstellen
Microsoft SQL Server
In Microsoft SQL Server beschränkt sich die Funktion, eine Kopie einer Tabelle zu erstellen lediglich auf das Schema und ist – aus meiner Sicht – eher schlecht umgesetzt. Es funktioniert nur mit einem SELECT … INTO
/* Create the new relation if it does not exist */
IF OBJECT_ID(N'dbo.new_customer', N'U') IS NULL
SELECT * INTO dbo.new_customer FROM dbo.customer;
GO
/* If the new relation should not have any data use a WHERE-statement */
IF OBJECT_ID(N'dbo.new_customer', N'U') IS NULL
SELECT * INTO dbo.new_customer FROM ERP_Demo.dbo.customer
WHERE 1 = 0;
GO
Das Prinzip ist einfach: Ein SELECT INTO überträgt die Metadaten und (optional) die Datensätze in eine neue Tabelle. Aus diesem Grund muss im Vorfeld geprüft werden, ob das Objekt bereits existiert. Ein SELECT…INTO schlägt fehl, wenn das Objekt bereits existiert! Sofern nur bestimmte Attribute aus der Quelle in der neuen Tabelle verwendet werden sollen, muss das SELECT mit den entsprechenden Attributen angegeben werden. Beeindruckend, oder?
PostgreSQL
In PostgreSQL wird eine neue Tabelle mit den Daten ebenfalls mit einem SELECT … INTO erstellt. Häufig kommt es aber vor, dass man nur die Metadaten benötigt, um z. B. Testdaten zu speichern, die als Basis dienen. Da ist PostgreSQL eindeutig im Vorteil!
/* OMG - I love this simple syntax! */
CREATE TABLE IF NOT EXISTS dbo.new_customer (LIKE dbo.customer);
/*
But maybe you want to have not only the meta data of the table
but all indexes from the source table, too!
*/
CREATE TABLE IF NOT EXISTS dbo.new_customer
(
LIKE dbo.customer
INCLUDING INDEXES
);
Beindruckt? Wenn das schon beeindruckend ist, dann wird die letzte Option ein Aufschrei des Glücks sein. Sehr häufig kommt es bei dem Design von Datenbankstrukturen vor, dass man eine Basistabelle (Auslieferung) hat, die für alle Kunden gleich ist. Individuelle Attribute werden dann in „customer relations“ ausgelagert, die mit einer 1:1 Verknüpfung wieder zusammengefügt werden. Daraus ergibt sich vor allen Dingen ein Problem, dass die Objekte immer mit einem JOIN verbunden werden müssen um anschließend die Daten zu präsentieren.
Vererbung von Strukturen
Microsoft SQL Server
Unmöglich – hier kann man sich nur eines Tricks bedienen, der sehr aufwändig ist. Folgendes Beispiel ist gegeben:
- Eine Kundentabelle besitzt eine vordefinierte Liste von Attributen
- Jede Ergänzung durch den Kunden wird in einer separaten Tabelle, die über den Primärschlüssel mit der Kundentabelle verbunden ist, gespeichert
- Es muss ein zentrales Objekt geben, über das die Ausgabe/Eingabe erfolgen soll
/* Let's create the table for the additional attributs first */
CREATE TABLE dbo.customer_details
(
c_custkey BIGINT NOT NULL,
C1 VARCHAR(10) NULL,
C2 VARCHAR(10) NULL,
CONSTRAINT pk_customer_details PRIMARY KEY CLUSTERED (c_custkey),
CONSTRAINT fk_customer FOREIGN KEY (c_custkey)
REFERENCES dbo.customer (c_custkey)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO
/* Let's create the view for the presentation layer */
CREATE OR ALTER VIEW dbo.v_customer
AS
SELECT c.c_custkey,
c.c_mktsegment,
c.c_nationkey,
c.c_name,
c.c_address,
c.c_phone,
c.c_acctbal,
c.c_comment,
cd.C1,
cd.C2
FROM dbo.customer AS c
LEFT JOIN dbo.customer_details AS cd
ON (c.c_custkey = cd.c_custkey)
GO
/* run an update on the record for customer 10 */
UPDATE dbo.v_customer
SET c_mktsegment = 'Uwe',
C1 = 'Testíng'
WHERE c_custkey = 10;
GO
/* BAMMMM!!!! */
Ein echtes Problem, dass sich LEIDER nur durch drei Möglichkeiten lösen ließe:
- Die Applikation steuert die Verwaltung der DML-Operationen (BITTE NICHT!!!)
- DML-Operationen werden durch eine Stored Procedure gesteuert
- DML-OPerationen werden durch einen INSTEAD OF Trigger gesteuert
Keine der Lösungen scheint mir ideal für dieses Szenario. Jedes mal, wenn der Kunde eine Erweiterung der individuellen Attribute durchführt, müssen für alle 3 genannten Optionen Anpassungen vorgenommen werden. Das ist umständlich und fehleranfällig.
PostgreSQL
Erst mal Durchatmen ob des erheblichen Aufwands, der in Microsoft SQL Server betrieben werden muss. Wie löst PostgreSQL diese Herausforderung? Easy! Ein Beispiel, das ich häufig in Anwendungen sehe ist folgendes Szenario:
- Eine Tabelle beinhaltet alle Kunden
- In einer separaten Tabelle werden alle Zulieferer gespeichert
Obwohl beide Tabellen fast identische Strukturen besitzen, müssen sie explizit angelegt werden. In PostgreSQL kann man sich einen nicht unerheblichen Aufwand sparen
/* Create the table for the customers */
CREATE TABLE IF NOT EXISTS dbo.customer
(
Id BIGINT NOT NULL,
name VARCHAR(64) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
City VARCHAR(64) NOT NULL,
CONSTRAINT pk_customer PRIMARY KEY (Id)
);
/* and create the table for the suppliers which has the same structure */
CREATE TABLE IF NOT EXISTS dbo.supplier
(
supplier_segment VARCHAR(64) NOT NULL
)
INHERITS (dbo.customer);
In der Applikation werden die Daten über unterschiedliche Funktionen eingetragen. Interessant ist, dass die Tabelle dbo.supplier identische Strukturen aus der – vererbten – Struktur von dbo.customer übernimmt.
/* Insert a new customer */
INSERT INTO dbo.customer (Id, name, ZIP, City)
VALUES (1, 'db Berater GmbH', '64390', 'Erzhausen');
/* Insert a new suplier */
INSERT INTO dbo.supplier (Id, name, ZIP, City, supplier_segment)
VALUES (1, 'Microsoft GmbH', '8000', 'München', 'Software');
Zunächst wird ein neuer Kunde eingetragen und im Anschluss wird ein neuer Zulieferer eingetragen. Da die Tabelle dbo.supplier die Struktur von dbo.customer übernommen hat, kennt die Tabelle die Attribute und ein neuer Zulieferer wird eingetragen. Wie werden die Daten gespeichert?
/* Select data from different sources */
SELECT * FROM dbo.supplier;
SELECT * FROM dbo.customer;
SELECT * FROM ONLY dbo.customer;
Das erste Ergebnis überrascht nicht – es zeigt den neuen Zulieferer mit der ID = 1. Das zweite Ergebnis mag auf den ersten Blick verwirrend sein, da der Primärschlüssel Id = 1 doppelt vorhanden ist. Das liegt aber daran, dass Postgres die gespeicherten Daten aus dbo.supplier in der Tabelle dbo.customer hinterlegt hat und verwaltet. Möchte man nur die Daten, die ursprünglich in dbo.customer gespeichert wurden, muss der Operator ONLY verwendet werden.
Um ehrlich zu sein, überzeugt mich der Anwendungsbereich – noch – nicht wirklich. Statt mit solchen „Krücken“ zu arbeiten, sollte man sich mehr Gedanken um ein funktionierendes Datenmodell machen. Aber dennoch begeistert mich die Möglichkeit, dass es geht!
Hinweis
All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with
https://www.postgresql.org/docs/current/ddl-inherit.htmlNO INHERITclauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
Vielen Dank fürs Lesen!
