PostgreSQL: extend table with additional calculated values

Question

as result of complicated select I have the_table with the structure like:

pl_id, date, value

I also have another table (let it calls the_source) where I have a list of all vailable pl_id. The idea is to extend the_table with values from the_source which doesn't exist. The problem is that some columns must be defined exactly as expected, another - can be any (let it be 0).

CREATE TABLE the_table (
  "pl_id" INTEGER,
  "date" DATE,
  "value" INTEGER
);

INSERT INTO the_table
VALUES
  ('1', '2024-05-01', '10'),
  ('1', '2024-06-01', '11'),
  ('2', '2024-05-01', '12'),
  ('2', '2024-06-01', '13');
  

CREATE TABLE the_source (
  "pl_id" INTEGER,
  "msg" TEXT
 );
 
 INSERT INTO the_source
 VALUES
    ('1', 'aaaaa'),
    ('2', 'bbbbb'),
    ('3', 'ccccc'),
    ('4', 'ddddd');

missed records can be found by select:

select * from the_source
where not exists (
  select
    *
  from the_table
  where the_table.pl_id = the_source.pl_id)

I would like to have the_final table with folling information:

  ('1', '2024-05-01', '10'),
  ('1', '2024-06-01', '11'),
  ('2', '2024-05-01', '12'),
  ('2', '2024-06-01', '13')
  ('3', '2024-05-01', '0'),
  ('3', '2024-06-01', '0'),
  ('4', '2024-05-01', '0'),
  ('4', '2024-06-01', '0');

Is it possible realize by SQL commands?

Answer 1

Yes, that is possible, the following shows one approach. (see demo)

with the_dates (adate) as
        (select distinct date
           from the_table
        ) 
   , msg_dates (pl_id,msg, adate) as 
       (select s.pl_id,s.msg, adate
          from the_source s
          cross join the_dates
       )
select m.pl_id, m.adate date, coalesce(t.value,0) value 
  from msg_dates  m
  left join the_table t 
    on (t.pl_id, t.date) = (m.pl_id, m.adate) 
 order by m.pl_id, m.adate;

What it does:
First it generates a CTE the_dates which extracts each interested data from the_table. It then generate a second CTE msg_dates which combines the interested dates with each entry from the_source. Finally, the main query, joins the second CTE back to the table filling on the missing values.

NOTE: It is generally considered poor using data types and/or reserved words as column names. Date is a Postgres data type and a SQL Standard reserved word (see documentation), Value is also a SQL Standard reserved word. For demo I changed them to t... respectively.