Recursive CTE in Postgresql

>> YOUR LINK HERE: ___ http://youtube.com/watch?v=WOWRlrvqg1w

Hello everyone! I hope this video has helped solve your questions and issues. This video is shared because a solution has been found for the question/problem. I create videos for questions that have solutions. If you have any other issues, feel free to reach out to me on Instagram: / ky.emrah • Below, you can find the text related to the question/problem. In the video, the question will be presented first, followed by the answers. If the video moves too fast, feel free to pause and review the answers. If you need more detailed information, you can find the necessary sources and links at the bottom of this description. I hope this video has been helpful, and even if it doesn't directly solve your problem, it will guide you to the source of the solution. I'd appreciate it if you like the video and subscribe to my channel!Recursive CTE in Postgresql • 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. • 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. • 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 | • +----------------+------------+----------+--------+---------------------+ • 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 • 2021-05-01 • 2021-06-01 • 2021-07-01 • I tried changing the data type, but I couldn't fiSource of the question: • https://stackoverflow.com/questions/7... • Question and source license information: • https://meta.stackexchange.com/help/l... • https://stackoverflow.com/

#############################

New on site