I'm trying to insert rows when there are missing dates in every ID as suggested in the title, by using the date range as the MIN and MAX date. Here is an example of the table that I have:

And I want to insert rows so that information that is not available turns None or Null, like this:

If you can see, the rows inserted are for every date missing in every "device_id", and columns "speed" and "height" enter as "None", so that I have information for every date in the range of dates used in the first table. Thank you!

CodePudding user response：

Solution :

SELECT i.device_id
     , d.date
     , CASE WHEN t.date IS NULL THEN 'None' ELSE t.speed :: text END AS speed 
     , CASE WHEN t.date IS NULL THEN 'None' ELSE t.height :: text END AS height 
  FROM 
     ( SELECT generate_series(min(date), max(date), interval '1 day') AS date
       FROM your_table
     ) AS d
 CROSS JOIN
     ( SELECT DISTINCT device_id
         FROM your_table
     ) AS i
  LEFT JOIN your_table AS t
    ON t.date = d.date
   AND t.device_id = i.device_id
 ORDER BY i.device_id, d.date

Result :

device_id   date                    speed   height
1           2021-06-07 00:00:00 01  None    None
1           2021-06-08 00:00:00 01  50      10
1           2021-06-09 00:00:00 01  30      15
1           2021-06-10 00:00:00 01  None    None
1           2021-06-11 00:00:00 01  None    None
1           2021-06-12 00:00:00 01  20      20
2           2021-06-07 00:00:00 01  10      15
2           2021-06-08 00:00:00 01  None    None
2           2021-06-09 00:00:00 01  None    None
2           2021-06-10 00:00:00 01  60      5
2           2021-06-11 00:00:00 01  None    None
2           2021-06-12 00:00:00 01  None    None

see the demo in dbfiddle

CodePudding user response：

As @stickybit suggests, using step-by-step CTEs:

with dates("date") as 
(
  select generate_series('2021-06-07'::timestamp, '2021-06-12', interval '1 day')::date
),
device_ids(device_id) as 
(
  select distinct device_id from the_table
),
fulllist as 
(
  select * 
  from dates cross join device_ids
)
select device_id, "date", coalesce(speed::text,'None') speed, coalesce(height::text,'None') height 
from fulllist 
left join the_table using (device_id, "date")
order by device_id, "date";

I do not think however that text is a good choice for datatype of speed and height as well as the word 'None' for missing values. Simple

select device_id, "date", speed, height

yielding nulls for missing values would be far more relevant.