Category “PostgreSQL” — 131 articles



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.


Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL text data type. The first notion to understand when processing text in any program is of course the notion of encoding.

So when addressing the text datatype we must mention encoding settings, and possibly also issues. An encoding is a particular representation of characters in bits and bytes. In the ASCII encoding the letter A is encoded as the 7-bits byte 1000001, or 65 in decimal, or 41 in hexadecimal. All those numbers are going to be written the same way on-disk, and the letter A too.


Today, we’re going to begin a dive into the PostgreSQL Data Types. As my colleague Will Leinweber said recently in his talk Constraints: a Developer’s Secret Weapon that he gave at pgDay Paris: database constraints in Postgres are the last line of defense.

The most important of those constraints is the data type, or the attribute domain in normalization slang. By declaring an attribute to be of a certain data type, then PostgreSQL ensures that this property is always true, and then implements advanced processing features for each data type, so that you may push the computation to the data, when needed.

This article is the first of a series that will go through many of the PostgreSQL data types, and we open the journey with boolean.

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer

France