JSON Schema validation in PostgreSQL?

There is another PostgreSQL extension that implements json validation. The usage is almost the same as “Postgres-JSON-schema”

CREATE TABLE example (id serial PRIMARY KEY, data jsonb);
-- do is_jsonb_valid instead of validate_json_schema
ALTER TABLE example ADD CONSTRAINT data_is_valid CHECK (is_jsonb_valid('{"type": "object"}', data));

INSERT INTO example (data) VALUES ('{}');
-- INSERT 0 1

INSERT INTO example (data) VALUES ('1');
-- ERROR:  new row for relation "example" violates check constraint "data_is_valid"
-- DETAIL:  Failing row contains (2, 1).

I’ve done some benchmarking validating tweets and it is 20x faster than “Postgres-JSON-schema”, mostly because it is written in C instead of SQL.

Disclaimer, I’ve written this extension.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)