Troubleshooting a Postgres SQL error when querying a foreign data table

Data and Databases
1

I’m having trouble with a Postgres query that involves a foreign data table. The table was set up using tds_fdw to link a SQL View from an MSSQL server.

When I try to run a query that joins this foreign table with other local tables, I get an error message. It says there’s a problem with the syntax near the ‘as’ keyword. Here’s a simplified version of the query:

SELECT col_a, col_b, col_c FROM (
    SELECT t1.col_a, t2.col_b, ft.col_c 
    FROM local_table1 t1 
    JOIN local_table2 t2 ON t1.id = t2.id
    JOIN foreign_table ft ON t2.id = ft.code 
    WHERE t1.status = 'active'
    UNION ALL
    SELECT t1.col_a, t3.col_b, ft.col_c 
    FROM local_table1 t1 
    JOIN local_table3 t3 ON t1.id = t3.id
    JOIN foreign_table ft ON t3.id = ft.code 
    WHERE t1.status = 'active'
) AS combined_data
WHERE combined_data.sequence = '1234'

The query worked fine before I replaced a local table with this foreign table. Any ideas on what might be causing this and how to fix it?

2

hmm, interesting issue! have you tried simplifying the query to isolate where the problem occurs? maybe start with just one join and add complexity gradually. also, are you sure the foreign table columns match exactly with the original local table? sometimes small discrepancies can cause unexpected errors. what other troubleshooting steps have you tried so far?

3

yo, that’s a tricky one! have u checked if the foreign table’s structure matches wat ur query expects? sometimes mssql to postgres conversions can be finicky. also, try running EXPLAIN on ur query - it might give u some clues bout where its choking. good luck troubleshooting!

4

I’ve encountered similar issues when working with foreign tables in Postgres. One potential cause could be incompatible data types between the MSSQL view and the Postgres foreign table. Verify that the data types in your foreign table definition match those in the original MSSQL view. Additionally, check if there are any constraints or default values on the foreign table that might be causing conflicts. It’s also worth examining the foreign table’s options to ensure they’re correctly set up for your specific use case. If the problem persists, you might need to create a wrapper view in Postgres to handle any incompatibilities between the two systems.