Recursive CTE in Postgresql

Question

When I run this request I get an error

recursive query "cte_0" column 1 has type date in non-recursive term but type timestamp without time zone overall LINE 4: select min(to_char(date_trunc('month', day), 'yyyy-mm-d...
^ HINT: Cast the output of the non-recursive term to the correct type.

In this query, the "day" column is a column with a data type datetime.

Query:

with recursive cte_0 as
(
    select  min(to_char(date_trunc('month', day), 'yyyy-mm-dd')::date) as dt
    from Transactions
    union all
    select dt + interval '1 month' 
    from cte_0
    where dt < (select  max(to_char(date_trunc('month', day), 'yyyy-mm-dd')::date) from Transactions )
)
select * from cte_0

+----------------+------------+----------+--------+---------------------+
| transaction_id | account_id | type     | amount | day                 |
+----------------+------------+----------+--------+---------------------+
| 2              | 3          | Creditor | 107100 | 2021-06-02 11:38:14 |
| 4              | 4          | Creditor | 10400  | 2021-06-20 12:39:18 |
| 11             | 4          | Debtor   | 58800  | 2021-07-23 12:41:55 |
| 1              | 4          | Creditor | 49300  | 2021-05-03 16:11:04 |
| 15             | 3          | Debtor   | 75500  | 2021-05-23 14:40:20 |
| 10             | 3          | Creditor | 102100 | 2021-06-15 10:37:16 |
| 14             | 4          | Creditor | 56300  | 2021-07-21 12:12:25 |
| 19             | 4          | Debtor   | 101100 | 2021-05-09 15:21:49 |
| 8              | 3          | Creditor | 64900  | 2021-07-26 15:09:56 |
| 7              | 3          | Creditor | 90900  | 2021-06-14 11:23:07 |
+----------------+------------+----------+--------+---------------------+

I want to get:

2021-05-01
2021-06-01
2021-07-01

I tried changing the data type, but I couldn't fix this error

Why are you doing min/max over date strings instead of date values? — Adrian Klaver
– Adrian Klaver, Commented Sep 29, 2024 at 15:34
1) That does not the answer the question of why you are using to_char() to turn the dates into strings instead of just doing min/max on the dates themselves. 2) What are you trying to achieve with this? Add example of expected output for a given input. 3) I have a feeling you will need to do with recursive cte_0(dt) as .... — Adrian Klaver
– Adrian Klaver, Commented Sep 29, 2024 at 15:39
Have you read the error message carefully? Because it kind of says everything you need already... The "non-recursive term" refers to what is before the union all, where you select something you cast as a date. Then you do select dt + interval '1 month' which, as per the message, is a timestamp without time zone. The message therefore invites you to "Cast the output of the non-recursive term to the correct type." (timestamp without time zone). Have you tried that? Or tried the equally-valid cast of the recursive part to date. As long as both halves are the same type... — Atmo
– Atmo, Commented Sep 29, 2024 at 16:05

Laurenz Albe · Accepted Answer · 2024-09-29 16:09:48Z

0

If you add an interval to a date, the result will be a timestamp without time zone. PostgreSQL complains that these data types are different.

I recommend that you use timestamp in the CTE and cast to date in the main query:

WITH RECURSIVE cte_0 AS (
    SELECT min(date_trunc('month', day)) AS dt
    FROM transactions

    UNION ALL

    SELECT dt + interval '1 month' 
    from cte_0
    where dt < (SELECT max(date_trunc('month', day)) FROM transactions)
)
SELECT CAST (dt AS date) FROM cte_0;

answered Sep 29, 2024 at 16:09

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2024-09-29 16:51:06Z

0

To be perfecly honest, you would be far better just using generate_series to generate these rows, rather than messing with recursive CTEs, which are inefficient.

Also, rather than doing min(to_char(date_trunc('month', day), 'yyyy-mm-dd')::date), it's almost certainly better to do the min and max first, like date_trunc('month', min(day))::date.

select
  g.dt::date as dt
from generate_series(
    (select date_trunc('month', min(t.day)) from Transactions t),
    (select date_trunc('month', max(t.day)) from Transactions t),
    interval '1 month'
) g(dt);

It might be faster to do the min and max in a single query, rather than two separate subqueries, but it depends on indexing.

answered Sep 29, 2024 at 16:51

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Comments

JohnH · Accepted Answer · 2024-09-29 17:07:24Z

0

The error message identifies a solution: i.e., cast the expression dt + interval '1 month' to DATE:

with recursive cte_0 as
(
    select  min(to_char(date_trunc('month', day), 'yyyy-mm-dd')::date) as dt
    from Transactions
    union all
    select (dt + interval '1 month')::DATE 
    from cte_0
    where dt < (select  max(to_char(date_trunc('month', day), 'yyyy-mm-dd')::date) from Transactions )
)
select * from cte_0;

Instead of using a recursive CTE, use GENERATE_SERIES:

SELECT
  GENERATE_SERIES(
    DATE_TRUNC('month', MIN(transactions.day)),
    DATE_TRUNC('month', MAX(transactions.day)),
    INTERVAL '1' MONTH
  )::DATE AS dt
FROM
  transactions;

This approach is usually more efficient than using a recursive CTE. If dt is to be used in other expressions that involve timestamps, then remove ::DATE to avoid unnecessary type cast and only cast to DATE when required.

Consider renaming the column day in transactions to avoid using a SQL reserved word as a user identifier.

edited Sep 29, 2024 at 17:07

answered Sep 29, 2024 at 16:48

JohnH

3,2932 gold badges6 silver badges20 bronze badges

1 Comment

Charlieface Over a year ago

Interesting that the optimizer actually manages to convert your query into mine if there is an index dbfiddle.uk/m9lsW8Gq

Erwin Brandstetter · Accepted Answer · 2024-09-29 23:30:53Z

0

You already got an explanation and better queries. generate_series() is much cheaper than a recursive CTE. This query is even cheaper:

SELECT generate_series(date_trunc('month', min(the_day))
                     , max(the_day)  -- no need to truncate
                     , interval '1 month')::date AS dt
FROM   transactions;

fiddle

As demonstrated in the manual, generate_series() stops before the next step would exceed the "stop" argument. Any timestamp of the final month does the job and we don't need to apply date_trunc() to $2, the "stop" argument.

answered Sep 29, 2024 at 23:24

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Recursive CTE in Postgresql

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related