Today it’s time to conclude our series of PostgreSQL Data Types articles with a recap. The series cover lots of core PostgreSQL data types and shows how to benefit from the PostgreSQL concept of a data type: more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions.
This allows an application developer to rely on PostgreSQL for more complex queries, having the processing happen where the data is, for instance when implementing advanced JOIN operations, then retrieving only the data set that is interesting for the application.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL Point type.
In order to put the Point datatype in a context where it makes sense, we’re going to download a complete geolocation data set and normalize it, thus making good use of both the normalization good practice and those other PostgreSQL data types we’ve been learning about in the previous articles of this series.
Buckle-up, this is a long article with a lot of SQL inside.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL ENUM type.
This data type has been added to PostgreSQL in order to make it easier to support migrations from MySQL. Proper relational design would use a reference table and a foreign key instead.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL JSON type.
PostgreSQL has built-in support for JSON with a great range of processing functions and operators, and complete indexing support. The documentation covers all the details in the chapters entitled JSON Types and JSON Functions and Operators.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL XML type.
The SQL standard includes a SQL/XML which introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database, as per the Wikipedia page.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL array data types.
Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL ranges data type.
Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value. This allows PostgreSQL to implement a concurrent safe check against overlapping ranges, as we’re going to see in this article.
Continuing our series of PostgreSQL Data Types today we’re going to introduce network address types.
PostgreSQL includes support for both cidr, inet, and macaddr data types. Again, those types are bundled with indexing support and advanced functions and operator support.
Continuing our series of PostgreSQL Data Types today we’re going to introduce date and time based processing functions.
Once the application’s data, or rather the user data is properly stored as timestamp with time zone, PostgreSQL allows implementing all the processing you need to. In this article we dive into a set of examples to help you get started with time based processing in your database. Can we boost your reporting skills?
Continuing our series of PostgreSQL Data Types today we’re going to introduce some of the PostgreSQL text processing functions.
There’s a very rich set of PostgreSQL functions to process text — you can find them all in the string functions and operators documentation chapter — with functions such as overlay(), substring(), position() or trim(). Or aggregates such as string_agg(). There are also regular expression functions, including the very powerful regexp_split_to_table(). In this article we see practical example putting them in practice.