Add a list of names to a generated series

Question

I generated a date series this way:

SELECT generate_series(date '2020-01-01', date '2021-01-21', '1 day');

Additionally I have a list with name (Max Meyer, Anna Smith, Peter Gardner). How could I add the name list to the date series, such that the name appears for every date in the series?

Vérace · Answer 1 · 2021-07-23T08:46:55.100

So, you have a RedShift instance - now, GENERATE_SERIES isn't fully supported, but it appears to at least work partially!

However, you are saying in comments that you are importing your date table into RedShift - so what I would do would be the following (all the code below is available on the fiddle here):

SELECT
  GENERATE_SERIES('2020-01-01'::DATE, '2020-01-15'::DATE, '1 DAY') AS dat;

Result (I reduced the date range to 15 days to make things legible - the same principle applies over a month, a year or even a decade...):

dat
2020-01-01 00:00:00+00
2020-01-02 00:00:00+00
2020-01-03 00:00:00+00
2020-01-04 00:00:00+00
...
... snipped for brevity
...
15 rows

In PostgreSQL 9.6 - the oldest supported version at the time of writing - we can do the following:

SELECT 
  nom.dat, name.nam 
FROM
  GENERATE_SERIES('2020-01-01'::DATE, '2020-01-15'::DATE, '1 DAY') AS nom (dat)
JOIN
  (VALUES  ('Max Meyer'), ('Anna Smith'), ('Peter Gardner')) AS name (nam) 
ON TRUE
ORDER BY  nom.dat, name.nam;

Result:

dat                     nam
2020-01-01 00:00:00+00  Anna Smith
2020-01-01 00:00:00+00  Max Meyer
2020-01-01 00:00:00+00  Peter Gardner
2020-01-02 00:00:00+00  Anna Smith
2020-01-02 00:00:00+00  Max Meyer
2020-01-02 00:00:00+00  Peter Gardner
...
... snipped for brevity
...
45 rows

So, what we have here is a CROSS JOIN between the dats from the GENERATE_SERIES and the nams in the the name table which we created using the VALUES clause. Effectively, I used the ON TRUE syntax which is the same as doing a CROSS JOIN.

Check out the cross-join tag here:

For any question involving a CROSS JOIN, i.e. combining every SELECT-ed field from one table with every SELECT-ed field from another.

From here, we have:

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

So, we have our 15 date fields CROSS JOIN-ed with our 3 names = 45 records in our resultset!

Now, if you've already created the dates table (see the fiddle) and you are simply importing it, you can do something like this:

SELECT d.dat, name.nam  FROM
dates d                   -- <<== imported date (from GENERATE_SERIES) table!
JOIN
  (VALUES  ('Max Meyer'), ('Anna Smith'), ('Peter Gardner')) AS name(nam) 
ON TRUE
ORDER BY d.dat, name.nam;

Result:

Same as for the CROSS JOIN above.

I've left a couple of alternative syntaxes/solutions using CROSS JOIN at the bottom of the fiddle - they may or may not be of interest to you.

Finally - from version 8.4, you may be able to use a CTE.

WITH name AS
(
  SELECT 'Max Meyer' AS nam
  UNION
  SELECT 'Anna Smith'
  UNION 
  SELECT 'Peter Gardner'
)
    SELECT d.dat, name.nam  FROM
    dates d                   -- <<== imported date (from GENERATE_SERIES) table!
    CROSS JOIN name.nam

which should give the same result (untested). If CTE's don't work on Redshift, you could just JOIN to the table of SELECTs with UNIONs... as a subquery. Thanks to @mustaccio for pointing out that "VALUES list used as constant tables..." isn't supported by Redshift.

Add a list of names to a generated series

1 Answers1