pg_jsonschema
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_jsonschema | 0.3.4 | FEAT | Apache-2.0 | Rust |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2760 | pg_jsonschema | No | No | No | Yes | No | No | - |
| Related | pg_graphql jsquery plv8 jsonb_plperl http pg_net pg_summarize pg_tiktoken |
|---|
manual update from 0.16.0 by Vonng
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 0.3.4 | 1817161514 | pg_jsonschema | - |
| RPM | PIGSTY | 0.3.4 | 1817161514 | pg_jsonschema_$v | - |
| DEB | PIGSTY | 0.3.4 | 1817161514 | postgresql-$v-pg-jsonschema | - |
Build
You can build the RPM / DEB packages for pg_jsonschema using pig build:
pig build pkg pg_jsonschema # build RPM / DEB packages
Install
You can install pg_jsonschema directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_jsonschema; # Install for current active PG version
pig ext install -y pg_jsonschema -v 18 # PG 18
pig ext install -y pg_jsonschema -v 17 # PG 17
pig ext install -y pg_jsonschema -v 16 # PG 16
pig ext install -y pg_jsonschema -v 15 # PG 15
pig ext install -y pg_jsonschema -v 14 # PG 14
dnf install -y pg_jsonschema_18 # PG 18
dnf install -y pg_jsonschema_17 # PG 17
dnf install -y pg_jsonschema_16 # PG 16
dnf install -y pg_jsonschema_15 # PG 15
dnf install -y pg_jsonschema_14 # PG 14
apt install -y postgresql-18-pg-jsonschema # PG 18
apt install -y postgresql-17-pg-jsonschema # PG 17
apt install -y postgresql-16-pg-jsonschema # PG 16
apt install -y postgresql-15-pg-jsonschema # PG 15
apt install -y postgresql-14-pg-jsonschema # PG 14
Create Extension:
CREATE EXTENSION pg_jsonschema;
Usage
pg_jsonschema: PostgreSQL extension providing JSON Schema validation
pg_jsonschema adds JSON Schema validation functions to PostgreSQL, enabling schema enforcement on JSON/JSONB columns via check constraints.
Functions
| Function | Description |
|---|---|
json_matches_schema(schema json, instance json) | Validate a JSON instance against a schema, returns boolean |
jsonb_matches_schema(schema json, instance jsonb) | Validate a JSONB instance against a schema, returns boolean |
jsonschema_is_valid(schema json) | Check whether a JSON schema itself is valid |
jsonschema_validation_errors(schema json, instance json) | Return an array of validation error messages |
Table Constraints
Use check constraints to enforce document structure:
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
Error Inspection
Retrieve detailed validation errors:
SELECT jsonschema_validation_errors('{"maxLength": 4}', '"123456789"');
-- Returns: {"\"123456789\" is longer than 4 characters"}
Schema Validation
Verify that a schema is well-formed before using it:
SELECT jsonschema_is_valid('{
"type": "object",
"properties": {
"name": {"type": "string"},
"age": {"type": "integer", "minimum": 0}
},
"required": ["name"]
}');
-- Returns: true
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.