Insert data into empty cells in ascending order

Question

I have a Postgres table with following structure:

CREATE TABLE tb1 (
id integer,
name text,
date date,
time time without tz
);

CREATE TABLE tb2 (
id integer,
name text,
date date
);

I need to generate a 3rd table tb3 that will have column time_now with increment steps by 10 minutes. If tb1.time is not the same as tb3.time_now then b2.name is filled in. If tb1.time equals (or close by time_now) it is inserted into tb3.
Example:

tb1

1, xxxx, 2014-10-01, 08:20:00
2, yyyy, 2014-10-01, 08:40:00

tb2

1, zzzz, 2014-10-01
2, vvvv, 2014-10-01
3, eeee, 2014-10-01

3rd table should look like:

1, 08:00:00,zzzz -----> from tb2
2, 08:10:00,vvvv -----> from tb2
3, 08:20:00,xxxx -----> from tb1
4, 08:30:00,eeee -----> from tb2
5, 08:40:00,yyyy -----> from tb1

How to achieve this?

Answer 1

SELECT t.id, t.time::text, COALESCE(t.name, t2.name) AS name
FROM  (
   SELECT g.id, g.time, t1.name
        , CASE WHEN t1.name IS NULL THEN
            row_number() OVER (PARTITION BY t1.name ORDER BY g.id)
          END AS rn
   FROM  (
      SELECT g AS id, '08:00'::time + '10 min'::interval * (g-1) AS time
      FROM   generate_series (1,6) g
      ) g
   LEFT JOIN tb1 t1 USING (time)
   ) t
LEFT   JOIN tb2 t2 ON t2.id = t.rn
ORDER  BY t.id;

1. First build a table of desired times with generate_series(). From 8:00 to 8:50 in my example.

2. Join to tb1 on time. Attach ascending numbers to empty slots with row_number() (rn).

3. Join tb2 to the remaining empty slots in ascending order.

4. Use COALESCE to pick names from tb1 and tb2.

Be wary about off-by-1 errors.

SQL Fiddle.

Aside: I would use none of those column names. id and name are not descriptive - never use those, be more specific. date and time are basic type names.