Forum Discussion
Power BI DirectQuery to Redshift: ODBC error 42883 with pg_catalog.date_add(...) does not exist
I'm using Power BI Desktop with Amazon Redshift in DirectQuery mode. Data loads initially (sometimes at least / sometimes it has errors), but when I scroll down in a visual (e.g., table), I eventually hit this error:
ODBC Error: ERROR [42883] ERROR: function pg_catalog.date_add("unknown", double precision, timestamp without time zone) does not exist
It seems Power BI is trying to use a date_add function from pg_catalog, but that function doesn't exist in Redshift.
My question is: Is there a workaround or fix to prevent this error? Can I solve it by adjusting column types or applying transformations?
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
Power BI is generating a pg_catalog.date_add() call automatically. This is a MySQL-style function that isn't available in Redshift's PostgreSQL engine. This typically happens when Power BI loads more rows, such as when scrolling in a table visual, and it's not caused by any DAX or transformations you've applied.
To resolve this behavior, please try the following steps:
- Make sure you are using the latest versions of Power BI Desktop and the Redshift ODBC Driver, as previous versions may have translation issues.
- It is recommended to use the native Redshift connector rather than a generic ODBC connection. The native connector leverages Microsoft’s optimized SQL translation and does not rely on pg_catalog.date_add.
- Turn Off Auto Date/Time: Navigate to File > Options > Data Load > Time Intelligence and deselect Auto Date/Time for new files to stop automatic background date logic from being created.
- In Power Query, confirm all date columns are explicitly typed as Date (not DateTime or Text).
If the problem continues after following these steps, please use Performance Analyzer to capture the generated SQL and share a snippet that includes the pg_catalog.date_add call. This will help determine if the issue is related to the connector’s SQL translation layer and if it should be escalated as a product issue.
Also thank you grazitti_sapna and Praful_Potphode your detailed follow-up helped narrow this down to a connector-level translation issue rather than a data or DAX logic problem.
I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.
Thank you.
7 Replies
- grazitti_sapna
Super User
Hi Anonymous,
Try below fixes, Instead of DATEADD([Date], 1, MONTH) you can use
[NewDate] = EDATE([Date], 1)for Adding a day
[NewDate] = [Date] + 1
Instead of creating date table in Power BI you can create it in redshift to avoid any function mismatches
-
Create a date dimension table in Redshift with all the necessary calculations:
-
Next/previous day
-
Month offsets
-
Week numbers, etc.
-
-
Join this table in Power BI.
Use import mode instead of direct query if you can
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi, Thanks for the response.
However, I am actually not applying that function. Not even using it. I just have a plain table that has a date field. And the initial load of that table into my visualization table works. However, once I scroll down and PBI fetches more data, this error occurs.Btw, switching to import is not an option unfortunately.
-
- Praful_Potphode
Hi Anonymous ,
Usually these issues arise because of date columns.apparently date columns in cloud datawarehouse(like snowflake,redshift etc) accept the dates like '0000-01-01','9999-12-31' which causes issue while loading data in powerbi.so my apparoch would be to identify if we have this type of data in date columns.If yes, then try to handle it using sql functions.
If date column is not causing the issue, then try loading columns one by one in desktop which will eventually give you the culprit.
Try the approach and let me know.
Please give kudos or mark it as resolved once confirmed.
Thanks and Regards,
Praful
No, this kind of data is not there. It's only data between 2020 and 2030.
Further, the error message suggests that the issue is not the date range, but that
"pg_catalog.date_add"
is called, which eventually not exists in postgres/redshift. It rather is mysql dialect, which power bi seems to generate here.
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
Power BI is generating a pg_catalog.date_add() call automatically. This is a MySQL-style function that isn't available in Redshift's PostgreSQL engine. This typically happens when Power BI loads more rows, such as when scrolling in a table visual, and it's not caused by any DAX or transformations you've applied.
To resolve this behavior, please try the following steps:
- Make sure you are using the latest versions of Power BI Desktop and the Redshift ODBC Driver, as previous versions may have translation issues.
- It is recommended to use the native Redshift connector rather than a generic ODBC connection. The native connector leverages Microsoft’s optimized SQL translation and does not rely on pg_catalog.date_add.
- Turn Off Auto Date/Time: Navigate to File > Options > Data Load > Time Intelligence and deselect Auto Date/Time for new files to stop automatic background date logic from being created.
- In Power Query, confirm all date columns are explicitly typed as Date (not DateTime or Text).
If the problem continues after following these steps, please use Performance Analyzer to capture the generated SQL and share a snippet that includes the pg_catalog.date_add call. This will help determine if the issue is related to the connector’s SQL translation layer and if it should be escalated as a product issue.
Also thank you grazitti_sapna and Praful_Potphode your detailed follow-up helped narrow this down to a connector-level translation issue rather than a data or DAX logic problem.
I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.
Thank you.
