Community • 29. April 2022
PostgreSQL – virtuelle Felder mit „generated columns“
Generated Columns bringen die virtuellen Felder aus QGIS nach PostgreSQL und können eine sinnvolle Alternative zu Views und Update-Triggern sein.
Oftmals werden Daten zur Ansicht benötigt, die sich aus anderen Daten ergeben. Ein einfaches Beispiel ist die Berechnung der Länge von Straßen. Dabei soll die Länge nicht statisch in einer Spalte stehen, sondern sich automatisch aktualisieren, wenn die Geometrie geändert wird. Eine alt bekannte Lösung dafür sind Views, eine andere die Trigger-Funktionen.
Views eignen sich besonders gut, wenn aus (mehreren) vorhandenen Tabellen einzelne Spalten ausgewählt werden sollen und es vielleicht auch in den Ergebnisspalten berechnete Werte gibt:
CREATE VIEW v_roads_countryname_length AS
SELECT a.gid
,a.NAME
,a.country
,b.pop
,b.economy
,ST_Length(a.geom, true) AS laenge
,a.geom
FROM ne_10m_roads a
,ne_10m_admin_0_countries b
LEFT JOIN ne_10m_admin_0_countries ON a.country = b.NAME;Möchte man aber alle Spalten einer einzelnen Tabelle übernehmen und nur eine weitere berechnete Spalte hinzufügen, kann der Sinn hier einen View zu erstellen in Frage gestellt werden.
CREATE VIEW v_roads_length AS
SELECT gid
,scalerank
,featurecla
,type
, ...
, ...
, ...
,min_zoom
,min_label
,geom
,ST_Length(geom, true) AS laenge
FROM PUBLIC.ne_10m_roads;Hinzu kommt, dass ein View nicht ohne Weiteres beschreibbar ist, d.h. Editierungen müssen weiterhin auf der Original-Tabelle durchgeführt werden, oder es werden instead-of-update-, instead-of-insert-Trigger benötigt. Eine andere Möglichkeit wäre es die Spalte mit der Längenangabe in die Original-Tabelle einzufügen und den Inhalt bei jedem Schreiben in die Tabelle über Trigger zu berechnen und fest zu schreiben.
Wie gut man es da hat, wenn man nur mit QGIS als einzigem Client arbeitet - dort gibt es die virtuellen Felder, mit denen ein berechnetes Feld an eine vorhandene Tabelle angehängt werden kann. Könnte es so etwas nicht auch für PostgreSQL geben? Mal sehen….
Das ist genau das, was wir suchen! Verfügbar sind die Generated Columns ‚erst‘ seit Version 12, die gibt es inzwischen zwar auch schon über zwei Jahre, aber die Generated Columns sind noch relativ unbekannt. Vielleicht auch, weil vor Postgres-Updates unnötiger Weise oft zurückgeschreckt wird.
Eine vorhandene Tabelle kann einfach um eine berechnete Spalte erweitert werden:
ALTER TABLE PUBLIC.ne_10m_roads
ADD COLUMN length numeric GENERATED ALWAYS AS
(round(st_length(geom, true)::numeric / 1000,2)) STORED;Oder beim Erstellen der Tabelle:
CREATE TABLE IF NOT EXISTS public.ne_10m_roads
(
gid integer NOT NULL DEFAULT nextval('ne_10m_roads_gid_seq'::regclass),
...
geom geometry(MultiLineString,4326),
length numeric GENERATED ALWAYS AS
(round(((st_length((geom)::geography, true))::numeric / (1000)::numeric), 2)) STORED,
CONSTRAINT ne_10m_roads_pkey PRIMARY KEY (gid)
)Der Zusatz STORED gibt an, dass die berechneten Daten gespeichert werden, sie verbrauchen also Platz auf der Festplatte. Die Angabe verwirrt ein wenig, denn der in der Dokumentation alternativ genannte Parameter VIRTUAL ist auch in Version 14 (noch) nicht verfügbar („PostgreSQL currently implements only stored generated columns.“)
Eine weitere verschmerzbare Einschränkung ist, dass sich Generated Columns nur auf persistente Spalten beziehen können, also nicht auf andere Generated Colunms. Dafür ist es möglich einen Index auf ihnen zu generieren:
CREATE INDEX idx_length
ON public.ne_10m_roads (length ASC NULLS LAST);Darüber hinaus versteht es sich von selbst, dass die Generated Columns nicht direkt geschrieben werden können:
UPDATE PUBLIC.ne_10m_roads
SET length = 500;
ERROR:
FEHLER: Spalte »length« kann nur auf DEFAULT aktualisiert werden
DETAIL: Spalte »length« ist eine generierte Spalte.
Weitere Beiträge, die Dich interessieren könnten:
- Mehrere PostgreSQL-Instanzen auf einer PostgreSQL-Installation
- QGIS: Eigene Funktionen für den Ausdruckseditor erstellen
