Power BI DirectQuery to Redshift: ODBC error 42883 with pg_catalog.date_add(...) does not exist

[merlin1]

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?

[v-tsaipranay]

Hi @merlin1 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @merlin1 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay · Accepted solution]

Hi @merlin1 ,

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.

[Praful_Potphode]

Hi @merlin1 ,

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

[merlin1]

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.

[grazitti_sapna]

Hi @merlin1,

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!

[merlin1]

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.