jsonb field enforce inner value datatype in postgres

Question

Am using postgres 10.3

My jsonb field linkage in the table test_linkages has this typical schema

{
  "activity_5640": {
    "in_scope": 109,
    "out_activity": 5640
  },
  "activity_8032": {
    "in_scope": 110,
    "out_activity": 8032
  },
  "root": {
    "in_common_site": 7862,
    "in_sub_project": 123,
    "in_wbs": "ABC.40125",
    "out_network": 97573314
  }
}

How do I enforce for e.g. linkage->'root'->'in_sub_project' to always be integer or numeric?

I know how to enforce for non null.

ALTER TABLE test_linkages ADD CONSTRAINT sub_project_must_exist CHECK (linkage->'root' ? 'in_sub_project');

But I want to enforce it to always be numeric or integer

I know there are only 4 types that Postgres supports in a json document

I know I can enforce the type at the code level, but I was hoping to enforce it at the database level somehow

Kim Stacks · Accepted Answer · 2020-05-15 13:21:25Z

You can use a check constraint using jsonb_typeof()

ALTER TABLE test_linkages 
   ADD CONSTRAINT must_be_a_number 
   CHECK ( jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number')

Using is not distinct from will properly deal with non-existing keys as well, so you don't need the additional check constraint to check for the presence of the key.

Note this allows zero and decimal values.

Also, json format doesn't accept negative number in the first place, so no need to check.

integer and zero (no decimal values)

ALTER TABLE test_linkages ADD CONSTRAINT must_be_integer 
CHECK ( 
    jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number' 
and (linkage->'root'->>'in_sub_project')::numeric % 1 = 0);

positive integer only (no zero)

ALTER TABLE test_linkages ADD CONSTRAINT must_be_positive_integer 
CHECK ( 
    jsonb_typeof(linkage-> 'root' -> 'in_sub_project') is not distinct from 'number' 
and (linkage->'root'->>'in_sub_project')::numeric % 1 = 0 
and (linkage->'root'->>'in_sub_project')::numeric > 0
);

Kim Stacks · Accepted Answer · 2020-05-15 12:38:37Z

The answer by the horse is pretty good. https://stackoverflow.com/a/61819147/80353

Here are some other useful info, I found relevant to this topic.

see all the constraints

--- see all constraints of table
SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Credit to https://dba.stackexchange.com/a/214877/56827

Must be positive

ALTER TABLE test_linkages ADD CONSTRAINT sub_project_must_positive CHECK ((linkage->'root'->>'in_sub_project')::numeric > 0);

only positive integer

ALTER TABLE test_linkages ADD CONSTRAINT sub_project_must_integer CHECK (linkage->'root'->>'in_sub_project' ~ '^[1-9]+(\d+)?$');

only numeric strings (allow "123" and 12.3)

ALTER TABLE test_linkages ADD CONSTRAINT sub_project_must_numeric CHECK (linkage->'root'->>'in_sub_project' ~ '^\d+(\.\d+)?$'); -- allow 12.1

Credit to https://stackoverflow.com/a/52921876/80353

Drop constraint

ALTER TABLE test_linkages DROP CONSTRAINT <constraint_name>;

Credit to https://www.techonthenet.com/postgresql/unique.php

Collectives™ on Stack Overflow

jsonb field enforce inner value datatype in postgres

2 Answers 2

integer and zero (no decimal values)

positive integer only (no zero)

Comments

see all the constraints

Must be positive

only positive integer

only numeric strings (allow "123" and 12.3)

Drop constraint

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

integer and zero (no decimal values)

positive integer only (no zero)

Comments

see all the constraints

Must be positive

only positive integer

only numeric strings (allow "123" and 12.3)

Drop constraint

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related