APIs and HTTPversion 0.3.4

pg_jsonschema

Check json and jsonb values against a JSON Schema in a CHECK constraint.

What it is for

pg_jsonschema adds json_matches_schema and jsonb_matches_schema. Use them in a CHECK constraint to keep a flexible jsonb column from drifting: inserts that do not match the schema fail with a constraint violation (SQLSTATE 23514).

Enable it

sql

create extension if not exists pg_jsonschema with schema extensions;

Example

sql

alter table products add constraint attributes_shape check (
  jsonb_matches_schema(
    '{"type": "object",
      "properties": {"color": {"type": "string"}, "size": {"type": "integer"}},
      "required": ["color"]}',
    attributes
  )
);

Questions

How do I enable pg_jsonschema?

Run create extension if not exists pg_jsonschema with schema extensions; in the SQL editor, or switch it on from the Extensions page in the console. You do not need superuser access.

Which version of pg_jsonschema is installed?

0.3.4, on Postgres 17.6, as read from the running engine on 2026-10-08. Check yours with: select extversion from pg_extension where extname = 'pg_jsonschema';

Upstream project: github.com/supabase/pg_jsonschema

More apis and http extensions

pg_graphql A GraphQL API over your tables at /graphql/v1.
pg_net Send HTTP requests from SQL, triggers and cron jobs.

Try pg_jsonschema on Scribase All extensions