#JSON WITH POSTGRESQL COMMANDS HOW TO#
#JSON WITH POSTGRESQL COMMANDS FREE#

There is a lot you can do wrong when using JSON in PostgreSQL, especially if you are not acquainted with relational databases. Rather than defining a table with hundreds of columns for all the possible attributes, most of which will be NULL, we model the most frequent attributes with normal table columns and use a JSON for the rest:ĬREATE INDEX ON wares USING gin (attributes) ĬREATE INDEX ON wares USING gin (name gin_trgm_ops) Other attributes may be rare, like the type of power plug, or ambiguous, like the pitch in screws or tuning forks. There are some properties that all or most of the goods will have, like price, weight, manufacturer, number available or package size. To show an example of how JSON in the database can be used with benefit, let us consider a shop that sells all kinds of mixed goods. The less you need to process the data inside the database, the better it is to store them as JSON. Often it may be a good idea to store some attributes as regular table columns and others in a JSON.

Don’t store references to other tables in JSON attributes.

Don’t use JSON if you want constraints on the data.Avoid JSON if you want to join on some of the attributes.Don’t use JSON if you want to use attributes in complicated WHERE conditions.Avoid large JSON objects if you want to modify individual attributes.Don’t use JSON for data that can easily be stored in database tables.

For example, the majority of questions about PostgreSQL and JSON asked on Stackoverflow are about problems that arise from the use of JSON where it had better been avoided.įollow these guidelines when you consider using JSON in PostgreSQL:

#JSON WITH POSTGRESQL COMMANDS HOW TO#

It is just that many people don’t understand how to use it right. If all the above is wrong, should we use JSON in PostgreSQL at all?ĭon’t get me wrong: JSON support in PostgreSQL is a wonderful thing. That query can use the GiST index from the exclusion constraint we created above. With our junction table, the query becomes WHERE CAST(elem.j -> 'from' AS timestamp) > 'to' AS timestamp) > TIMESTAMP ' 15:30:00' Imagine we want to search for all rooms that are occupied at 15:30:00. Simple searches for equality can be performed with the JSON containment operator and such searches can be supported by a GIN index. Sixth mistake: complicated searches in JSON The extension is required to create a GiST index on a bigint column.

With the junction table, the exercise is simple all we have to do is to add an exclusion constraint that checks for overlaps with the & operator:ĬREATE EXTENSION IF NOT EXISTS btree_gist ĪLTER TABLE reservations ADD EXCLUDE USING gist (

#JSON WITH POSTGRESQL COMMANDS FREE#

The best that comes to mind is a constraint trigger, but that would require elaborate locking or the SERIALIZABLE transaction isolation level to be free from race conditions. With JSON, we are pretty much out of luck here. So far, our data model offers no protection against overlapping reservations, which would be good to enforce in the database. Fifth mistake: trying to enforce constraints on JSON This statement will only write a small amount of data.ĭeleting a reservation is just as complicated and expensive, and is left as an exercise to the reader. The whole JSON object has to be read and written, which is more I/O than you would want – particularly if the JSON object is large and stored out of line.Ĭompare how simple the same exercise would be with the junction table: This will fetch the complete JSON object, construct a new JSON from it and store that new object in the table. This data model exemplifies everything that you can do wrong: In this article, I will try to point out good and bad uses of JSON in PostgreSQL, and provide you with guidelines that you can follow. That causes problems and unhappiness in the long run. However, my experience is that the vast majority of people don’t use it correctly. Many people – particularly those with a stronger background in Javascript programming than in relational databases – use it extensively. The comprehensive JSON support in PostgreSQL is one of its best-loved features. Data modeling JSON postgresql table design

Json with postgresql commands