The Art of PostgreSQL/

...

/

Processing Function and Data Sources

Processing Function and Data Sources

Learn and practice processing functions in PostgreSQL.

We'll cover the following...

PostgreSQL embeds a very rich set of processing functions that can be used anywhere in the queries, even if most of them are more useful in the select clause. Because we see a lot of code fetching only the raw data from the RDBMS and then doing all the processing in the application code, we want to show an example query processing calendar-related information with PostgreSQL.

Functions for date and time

The following query is a showcase for extract() and to_char() functions, and it also uses the case construct. Read the documentation on date/time functions and operators for more details and functions on the same topic.

PostgreSQL
select date::date,
       extract('isodow' from date) as dow,
       to_char(date, 'dy') as day,
       extract('isoyear' from date) as "iso year",
       extract('week' from date) as week,
       extract('day' from
               (date + interval '2 month - 1 day')
              )
        as feb,
       extract('year' from date) as year,
       extract('day' from
               (date + interval '2 month - 1 day')
              ) = 29
       as leap
  from generate_series(date '2000-01-01',
                       date '2010-01-01',
                       interval '1 year')
       as t(date);

The generate_series() function returns a set of items here, all the dates of the first day of the year from the 2000s. For ...