QGIS, a Desktop GIS package, was a PostgresSQL/PostGIS client since its very beginning. The latest release, 3.14 codename “Pi”, along shiny new features, adds also bug fixes making use of Postgres’ GENERATED columns possible. This might not be a game-changer, but it definitely makes a PostGIS DBA’s life easier. Why? Read on.
Generated columns are similar in functionality to virtual fields – they are populated with computed values – but implemented at the database level. That means every application using the DB, be it another QGIS instance, Geoserver, web apps, even ArcGIS can use those columns, while virtual fields are a QGIS-only feature, and bound to a specific project. Threre are two kinds of generated columns, IDENTITY and user-defined.
IDENTITY is the new SERIAL
Every decent database has some method of generating row identifiers: MySQL has AUTO INCREMENT, Oracle has ROWID, MongoDB has _id and so on. In Postgres, the preferred way to deal with this issue was to use the SERIAL pseudo-type:
CREATE TABLE fields(id SERIAL PRIMARY KEY, name VARCHAR, geom GEOMETRY);This code generated a INTEGER column, a sequence named “fields_id_seq”, a DEFAULT value being the next value of sequence, and a PRIMARY KEY constraint (in fact, SERIAL doesn’t have to be a primary key, but that’s its main use case). The type name “serial” was gone and the column was treated as a regular INTEGER column.
It worked, but had its downsides: for example, the permissions for sequence have to be granted separately – with GRANT ALL or GRANT INSERT ON TABLE, but without GRANT USAGE ON SEQUENCE, the user wouldn’t be able to add new features.
The new approach is defined by SQL standard, implemented in Postgres 10 and newer, for which the syntax is:
CREATE TABLE fields(id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR, geom GEOMETRY);Indeed, more characters to type. But no “GRANT USAGE ON SEQUENCE…” anymore. Sadly, the GUI tools in QGIS haven’t caught up yet and creating the table using a form, or importing an existing layer into PostGIS will yield the old-fashioned SERIAL column.
User-defined generated columns
Especially in a spatial database, sometimes there is a requirement of a field having a computed value derived from other properties: X/Y coordinates, area, perimeter, or length to name a few. Before Postgres 12, this could be implemented using triggers:
ALTER TABLE fields ADD COLUMN area NUMERIC;
CREATE FUNCTION calc_area() RETURNS TRIGGER AS $$
BEGIN
NEW.area = ST_Area(NEW.geom);
RETURN NEW;
END;
$$
language plpgsql;
CREATE TRIGGER calc_area_trigger BEFORE INSERT OR UPDATE ON fields FOR EACH ROW EXECUTE PROCEDURE calc_area();For 12 and newer, there exists another possibility: generate the value using GENERATED syntax:
ALTER TABLE fields ALTER COLUMN area NUMERIC GENERATED ALWAYS AS (ST_Area(geom)) STORED;This means same “bang” (functionality) for less “buck” (code). No need to create a function and a trigger, just an expression and you are done. While QGIS currently doesn’t automatically block input fields in attribute form for such columns, any user-supplied value will be overwritten on session end (same as with triggers).
Of course, triggers aren’t going anywhere: for more complicated cases they will be still necessary. But for simple area/length/coordinate calculation, GENERATED columns offer a simpler, declarative approach.
So if you have a PostGIS instance based on Postgres 12, use QGIS, and haven’t upgraded your QGIS yet – this is one more reason to do so!