CODEWITHSUNDEEP
sqlpostgresql
Farzher
Farzher

Reputation: 14583

Postgres using a JSONB column instead of defined columns

Postgres has a lot of support for JSON (even indexing). I want to just put 1 jsonb field called everything instead of defining of bunch of typed columns (mongodb style).

But I assume this is a bad idea. My question is: Why shouldn't I do this? Is the only con just the fact that it'll probably take extra storage space?

(My table has a lot of optional fields which is why I want to do this)

Upvotes: 0

Views: 229

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24551

I can think of a few reasons:

  • If the JSON blob has foreign keys to other columns, I don't think you can add a REFERENCES constraint.

  • With columns you can enforce NOT NULL constraints very easily, but with JSON you'll have to write CHECK constraints for each required value.

  • JSON has fewer types than Postgres. For instance, no date/time/timestamp, no intervals, no ranges, just one kind of numeric type, no IP addresses, no binary data.

  • Over time if you add/move/delete fields in the JSON structure, you'll find that older rows become obsolete and have JSON your app doesn't understand. Using regular columns forces you to keep the whole table up-to-date.

Upvotes: 4

Related Questions