Coded value domains in PostgreSQL

Question

I'm using, at work, ArcGIS geodatabases and using coded value domains a lot.

Now I'm working with PostgreSQL and I'm trying to create the same sort of data model I would in, for example, a file gdb. And that includes domains.

Coded value domains do not have equivalent to PostgreSQL domains.

For Both I can set up value constraints but the first also prevent repeating long strings by the use of codes without visually losing that information (you see the string, not the code).

That's exactly what I want to accomplish and can't find the way to do it. Maybe it has no sense and it's just a problem of concept as maybe PostgreSQL data modelling simply has a way to approach or deal with this situation that I ignore.

So is this usually done in PostgreSQL? Can it be done without too much extra code? Does it even has sense? Please enlighten me on this.

An illustrative example,

I have a table with all the trees in a city:

city_trees

tree_id | t_location | t_species | t_health
--------+---------------+----------------+--------------
234 | Arc Cie Av. | Populus nigra | Healthy
235 | Arc Cie Av. | Populus nigra | Deceased
236 | 27th May Str. | Salix alba L. | Healthy
237 | Monolith Sqr. | Quercus alba | Healthy

For species names and health status I use coded value domains. The use of domains is transparent to the user: in the city_trees table I see the domain descriptions despite they are actually populated with coded values.

domSpecies

code | description
------+-----------------
PN | Populus nigra
SAL | Salix alba L.
QA | Quercus alba

domHealthStatus

code | description
-----+-----------------
H | Healthy
D | Declining
L | Deceased
C | Cut

Answer 1

You are correct that there is no equivalent in PostgreSQL to the coded value domain available in ArcGIS.

The simplest option may be to just create lookup tables, one for each domain, then map the code and description appropriately for each use. You can even apply constraints on the code and description columns to ensure input validation.

Here is an example replacing one of your domains in the question:

-- Create lookup table to replace the code/value domain of the same name
CREATE TABLE domHealthStatus
(
 code CHARACTER(1) NOT NULL PRIMARY KEY
 CHECK (code IN ('H', 'D', 'L', 'C')),
 description VARCHAR(50) NOT NULL
);
-- Populate the lookup table with unique code/value pairs
-- Note the use of check and primary key constraints to enforce uniqueness
INSERT INTO domHealthStatus VALUES
 ('H', 'Healthy'),
 ('D', 'Declining'),
 ('L', 'Deceased'),
 ('C', 'Cut');

Answer 2

It is possible to access the domain values from the geodatabase schema using SQL, where the domain definitions are stored as xml in the gdb_items table (by default installed under the sde schema).

From the documentation these examples show how to extract the domains in Oracle and MySQL, and to extract all domains in Postgres;

SELECT name as domain_name, 
unnest(CAST(xpath('//CodedValue/Name/text()',definition) AS text)::text[]) AS description,
unnest(CAST(xpath('//CodedValue/Code/text()',definition) AS text)::text[]) AS code
from sde.gdb_items

This can then either be adapted to exctract your species or healthstatus domain, or alternatively created as a view and used from there.