PostgreSQL comes jam-packed with exceptionally useful data types that Rails developers can take advantage of. Let’s have a look at some of our favorites.
The JSON/JSONB data type allows us to store and query JSON structures. Though both JSON/JSONB look the same, we recommend developers to use JSONB, as it’s the upgraded version of JSON.
The key difference between both is that JSON data is stored just as text, whereas JSONB stores it in a decomposed binary form. This makes querying more efficient and faster. It also allows JSON data to be directly indexed!
This datatype opens up the door to integrate relational and non-relational data without losing out on performance.
The column data type can either be
Let’s have a look at how to store and retrieve data.
The best part about these structures is that we can query them like any non-relational data.
However, Rails does not provide validations for these data types out of the box. activerecord_json_validator is a great alternative.
Before using this data type,
one must enable the
Though it is not too different from JSON/JSONB structures,
it is useful in certain scenarios.
The column data type here is
Hstore provides a more intuitive retrieval.
These data types can also be queried with ease.
Sometimes one might just need a simple datatype to store additional information in. If that’s the case, try using the array datatype! It’s pretty much self-explanatory.
Not only is one able to store data effectively, but one can also perform complex queries using this datatype.
Let’s dive into how to query this data.
bytea is PostgreSQL’s version of a BLOB,
which stands for Binary Large Object.
It can be a quick solution to store files directly in our database.
use this as sparingly as possible.
The bytea format is just a sequence of bytes — however, it is terrible for large amounts of binary data. To store a file in text in the database, we must escape all non-printable types. This means that a single null byte would need to be encoded as “\000” in the ASCII format. It causes an unnecessary expansion of data.
Further, it is near impossible to stream data directly from the database. Even while using an unescape filter to do so, it becomes increasingly inefficient. So much so that streaming a 2MB file would need somewhere around 6MB of data to run through the filter.
Couple this with the naïve method that runs all of the data through Ruby strings, this datatype balloons tremendously in memory.
The range datatype is a quick and simple way to store date, number, or any other set of data.
As always, querying this data is a piece of cake.
Similar to the range, this data type can store a Ruby representation of data. One can use this to store any ActiveSupport::Duration objects.
Apart from these major data types, PostgreSQL and Rails also provide additional data types built atop text columns like Composites and Enums. If one needs to store network-related information like IP address, mac address, and so on, it is useful to look into the Network Address data-types.