0

I have a column that have json datatype

how to make a constraint or validation in my postgresql to validate that this jsonb column value must be a hash/object/json

because apparently after some testing besides hash/json/object jsonb accept array and string too

1 Answer 1

1

You can add the following CHECK constraint

ALTER TABLE YourTable
  ADD CONSTRAINT OnlyJsonObject
  CHECK (jsonb_typeof(YourColumn) = 'object');

db<>fiddle

See also the documentation.

4
  • just want to make sure 'object' in here means json object right? just check it yeah 'object' means json object
    – buncis
    May 10, 2023 at 11:52
  • See fiddle dbfiddle.uk/52gLhcL2 May 10, 2023 at 12:19
  • so apparently my postgresql autocasting the check constraint into this ((jsonb_typeof(roles) = 'object'::text)) I have past the same expression to your dbfiddle and it still works fine, any commentary on that?
    – buncis
    May 11, 2023 at 17:06
  • It's normalizing the constraint so that the implicit conversion from varchar to text is explicit May 11, 2023 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.