Full geodatabase semantics

What it is

agol_fdw doesn't stop at fields and geometry. It translates each geodatabase construct into the Postgres mechanism that genuinely enforces it:

• Coded-value domains → lookup tables (dom_<field>) with foreign keys.
• Range domains → CHECK constraints.
• Subtypes → subtype lookup tables plus a consolidated BEFORE trigger enforcing per-subtype defaults and domain membership.
• Attribute rules — constraint rules become CHECK; calculation rules become BEFORE triggers; validation rules become deferred validation-issue logging; untranslatable rules are passed through and enforced by the worker.
• Relationships — 1:1 and 1:M become real foreign keys with matching delete behavior; M:N become junction tables (rel_<origin>_<dest>).
• Contingent values → combo lookup tables (cv_<fieldgroup>) validated by triggers.

Why it matters

Most "AGOL-to-Postgres" pipelines flatten everything to plain columns and lose the rules that make a geodatabase a geodatabase. agol_fdw keeps the semantics — so the integrity you relied on in ArcGIS is enforced by Postgres itself, in SQL.

See it

-- a coded-value domain becomes a real lookup + foreign key:
CREATE TABLE data.dom_status (
  code smallint PRIMARY KEY,
  label text NOT NULL
);

ALTER TABLE data.poles
  ADD CONSTRAINT poles_status_fk
  FOREIGN KEY (status) REFERENCES data.dom_status(code);

-- a range domain becomes a CHECK:
ALTER TABLE data.poles
  ADD CONSTRAINT poles_height_range CHECK (height BETWEEN 0 AND 50);

Related

← Back to home