Storing Semi-Structured Data in PostgreSQL

Traditionally, relational databases required all tables to have a fixed data schema, i.e. a set of attributes (such as a “user” table with attributes “firstName”, “lastName” and “email”). You could change the schema by adding and removing attributes, but the available attributes were always know at a given point in time.

For many use cases, this was acceptable. However, some applications have the need to store semi-structured data: entries where the attributes are not known ahead of time.

Since 2012, PostgreSQL allows to store semi-structured data inside tables in the JSON notation:

{
  "ID": "123",
  "Country": "Denmark",
  "Title": "How to store JSON in PostgreSQL"
}

This allows having different sets of attributes for different rows in your table while leaving your table schema constant.

Continue reading