How to Fill Missing Dates in PostgreSQL using generate_series

Last updated on May 17th, 2020 at 06:10 am

Often, we need to fill missing dates in PostgreSQL by filling missing rows for those date values. Here’s how to fill in missing dates in PostgreSQL using generate_series function.

How to Fill Missing Dates in PostgreSQL using generate_series

Here are the steps to fill missing dates in PostgreSQL. We will use generate_series in PostgreSQL to fill missing values.

Let us say you have the following table sales(order_date,sale)

postgresql=# create table sales(order_date date,sale int);

postgresql=# insert into sales(order_date,sale) values('2020-04-01',212),
     ('2020-04-04',220),
     ('2020-04-05',120),
     ('2020-04-07',200),
     ('2020-04-08',222),
     ('2020-04-10',312),
     ('2020-04-11',225),
     ('2020-04-12',212);

postgresql=# select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-04-01 |  212 |
| 2020-04-04 |  220 |
| 2020-04-05 |  120 |
| 2020-04-07 |  200 |
| 2020-04-08 |  222 |
| 2020-04-10 |  312 |
| 2020-04-11 |  225 |
| 2020-04-12 |  212 |
+------------+------+

Bonus Read : How to Calculate Median in PostgreSQL

As you can see in table above, there are missing date values in it such as April 2, 3, etc.

If we try to plot this data on a graph, it will look something like

Such a graph can be misleading as it doesn’t show column gaps for missing dates.

Bonus Read : How to Calculate Moving Average in PostgreSQL

To fill missing data in PostgreSQL, we need to create a ‘helper’ date series table that contains all dates between minimum and maximum dates in our table, including missing date values as rows. We will join this helper table with our sales table.

We will use generate_series in PostgreSQL to generate date series table. generate_series function automatically generates a continues series of numbers & dates, if you supply the min and max values for your series.

Here’s an example, where we tell generate_series to generate a date-series between minimum and maximum dates in our table, including missing date values as rows.

postgres=# SELECT generate_series(min(order_date), max(order_date), '1d')::date AS order_date
           FROM   sales;

 order_date
------------
 2020-04-01
 2020-04-02
 2020-04-03
 2020-04-04
 2020-04-05
 2020-04-06
 2020-04-07
 2020-04-08
 2020-04-09
 2020-04-10
 2020-04-11
 2020-04-12

Bonus Read : How to Import CSV File in PostgreSQL

Next, we do a LEFT join of helper table with sales to fill missing dates in PostgreSQL.

postgres=# SELECT x.order_date, t.sale
           FROM (
              SELECT generate_series(min(order_date), max(order_date), '1d')::date AS order_date
              FROM   sales
               ) x
           LEFT   JOIN sales t USING (order_date)
           ORDER  BY x.order_date;

 order_date | sale
------------+------
 2020-04-01 |  212
 2020-04-02 |
 2020-04-03 |
 2020-04-04 |  220
 2020-04-05 |  120
 2020-04-06 |
 2020-04-07 |  200
 2020-04-08 |  222
 2020-04-09 |
 2020-04-10 |  312
 2020-04-11 |  225
 2020-04-12 |  212

After you fill missing dates in PostgreSQL, you can use a reporting tool to plot this data on a bar chart or dashboard and share it with your team. Here’s an example of a bar chart that shows daily sales, created using Ubiq.