pg_jsonschema

pig build pkg pg_jsonschema;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_jsonschema;		# install via package name, for the active PG version

pig install pg_jsonschema -v 18;   # install for PG 18
pig install pg_jsonschema -v 17;   # install for PG 17
pig install pg_jsonschema -v 16;   # install for PG 16
pig install pg_jsonschema -v 15;   # install for PG 15
pig install pg_jsonschema -v 14;   # install for PG 14

CREATE EXTENSION pg_jsonschema;

CREATE TABLE customer (
    id serial PRIMARY KEY,
    metadata json,
    CHECK (
        json_matches_schema(
            '{
                "type": "object",
                "properties": {
                    "tags": {
                        "type": "array",
                        "items": {
                            "type": "string",
                            "maxLength": 16
                        }
                    }
                }
            }',
            metadata
        )
    )
);

-- Valid insert (passes check constraint)
INSERT INTO customer(metadata) VALUES ('{"tags": ["vip", "darkmode-ui"]}');

-- Invalid insert (rejected by check constraint)
INSERT INTO customer(metadata) VALUES ('{"tags": [1, 3]}');
-- ERROR: new row violates check constraint

SELECT jsonschema_validation_errors('{"maxLength": 4}', '"123456789"');
-- Returns: {"\"123456789\" is longer than 4 characters"}

SELECT jsonschema_is_valid('{
    "type": "object",
    "properties": {
        "name": {"type": "string"},
        "age":  {"type": "integer", "minimum": 0}
    },
    "required": ["name"]
}');
-- Returns: true