Webinar: Fantastic Data Types and Where to Use Them [Follow Up]

PostgreSQL has fantastic data types such as ENUM, ARRAY, and JSON, to name a few. These data types were added to cover use cases that were not easily solved with the existing types. Due to their post-relational nature, they remain unusual and even strange to some developers, but once you learn them, you can create unexpected solutions to specific use cases.

For instance, ENUM types are extremely good defining constraints to column values, providing a more descriptive design of your business model. JSON objects can store a variable amount of fields where it is not possible to define traditional tables.

In order to explore this topic in detail, 2ndQuadrant arranged a live webinar “Fantastic Data Types and Where to Use Them”. The session was hosted by Boriss Mejías, PostgreSQL Consultant, and Trainer.

Those who weren’t able to attend the live webinar can now view the recording here.

Due to limited time, some of the questions were not answered during the live webinar, so answers by the host are provided below:

Question: If we have a table with two columns, id and JSON, how can we correct that bad use of JSON?

Answer:  Note that we consider this as a bad use because all columns are put inside a JSONB object, when some of the fields have their own column. The idea is to identify which fields are present in all JSONB objects (or almost all of them), take them out of the JSONB, and put them in their own column. You will end up with a table with: id, a set of columns that belongs to almost all objects, and then a JSONB objects for the variable fields.

Question: It is possible in JSONB type to have differen estructure amog rows? For example one row is {“name”:”nombre”} an other row in the same table, same column is {“name”:”nombre”,”lastname”:”apellido”}

Answer:  Yes, it’s possible. The main advantage of the JSONB objects is to have a variable set of fields.

Question: When defining an ENUM type field, what space does it occupy each value? 4 bytes?

Answer:  It’s always 4 bytes, regardless the values you put on the ENUM

Question: When defining an ENUM type field, what space does it occupy each value? 4 bytes?

Answer:  It’s always 4 bytes, regardless the values you put on the ENUM

Question: When making the comparison you should put…

WHERE mood = ‘happy’

OR

WHERE mood = 1?

Answer:  WHERE mood = ‘happy’. That’s one of the advantages of ENUM. You do not worry about the internal implementation, you just used the values that make sense for your application logic. The results from the SELECT query will also have the values ‘meh’, ‘cool’, ‘happy’, directly. You don’t need to join tables or transform from integers to values.

To be the first to know about upcoming PostgreSQL webinars by 2ndQuadrant, visit our Webinars page.

For any questions, comments, or feedback, please visit our website or send an email to [email protected].