It's a quirk of how multiple set-returning functions are executed when invoked in the SELECT-list. You expect the result to be the cross product of the two, but that's not how it works. It's actually the lowest common multiple of the rowcounts of the two. See: Why do these join...

If you can guarantee there is no more than one row for every 10-min-slot in tb1, this should be fastest: SELECT * FROM ( SELECT g AS id, '09:00'::time + '10 min'::interval * (g-1) AS time_slot FROM generate_series (1,10) g ) g LEFT JOIN tb1 t1 ON t1.time >= g.time_slot...

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 )...

Create a new table with the three required columns: CREATE TABLE new_tbl ( nam varchar, loc varchar, dt date); Since you want to have records in the new table over the range start_date - end_date with a day interval, inclusive, the easiest solution is to generate a set from your...

generate_series() was the right idea. You probably did not get the details right. Could work like this: WITH cte AS ( SELECT entry_date, user_id, <other_stuff> FROM <tables, joins, etc> GROUP BY entry_date, user_id WHERE <whatever limits I want> ) SELECT * FROM (SELECT DISTINCT user_id FROM cte) u CROSS JOIN...

CROSS JOIN the latest date to generate_series(), followed by a LEFT JOIN to the main table. SELECT ARRAY( SELECT count(d.invoicedate) AS ct FROM ( SELECT max(invoicedate) AS last_date FROM dok WHERE invoicedate < current_date -- "maximum date before current date" ) l CROSS JOIN generate_series(0, 11*7, 7) AS g(days) LEFT...

The most efficient way should be to find the first Monday and generate a series in steps of 7 days: CREATE OR REPLACE FUNCTION f_mondays(dr daterange) RETURNS TABLE (day date) AS $func$ SELECT generate_series(a + (8 - EXTRACT(ISODOW FROM a)::int) % 7 , z , interval '7 days')::date FROM (...

In order to get the current location for each business_id for any given year you need two things: A parameterized query to select the year, implemented as a SQL language function. A dirty trick to aggregate on year, group by the business_id, and leave the coordinates untouched. That is done...