1

I have a Postgres SQL query that retrieves all the data I need apart from one field, which is not stored. I have to connect to a SQL Server database to retrieve this additional field, which I am doing using dblink. I've used dblink in a few other queries, but only connecting Postgres to Postgres.

 SELECT
    cm.course_id AS "EXTERNAL_COURSE_KEY",
    cm.course_id AS "COURSE_ID",
    cm.course_name AS "COURSE_NAME",
    cm.start_date AS "START_DATE",
    '' AS "END_DATE",
    cm.available_ind AS "AVAILABLE_IND",
CASE
        WHEN cm.row_status = '0' THEN
        'ENABLED' 
        WHEN cm.row_status = '1' THEN
        'UNDEFINED' 
        WHEN cm.row_status = '2' THEN
        'DISABLED' ELSE'DELETED' 
    END ROW_STATUS,
    'TEMPLATE ID' AS "TEMPLATE_COURSE_KEY",
CASE
        WHEN cm.duration = 'C' THEN
        'CONTINUOUS' 
        WHEN cm.duration = 'R' THEN
        'RANGE' 
    END DURATION 
FROM
    course_main cm 
CROSS JOIN dblink ( 'host=<HOST> port=<PORT NUM> dbname=<DB NAME> user=<USERNAME> password=<PASSWORD>',
    'SELECT mle_id, template_id from mle_object;' ) -- accessing sql server db
    AS course_templates ( mle_id VARCHAR(255), template_id VARCHAR(255) )
WHERE
    cm.course_id = 'I3016-AMBS-60037-1192-06M-027386'

When I run the query, I get the error below.

ERROR: could not establish connection DETAIL: could not connect to server: Connection timed out Is the server running on host "[HOST]" ([IP ADDRESS]) and accepting TCP/IP connections on port [PORT NUM]

I can retrieve data from this sql server database in a separate query, but not using dblink.

Any help is appreciated.

thanks

1 Answer 1

2

You can't. The dblink extension is only usable between Postgres instances.

If you need to access a SQL Server table, you will have to use a foreign data wrapper.

There is one for SQL Server (I only works on Linux if I'm not mistaken).

Once the wrapper is installed you can create a foreign table and join to that in your query.

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

1 Comment

Thanks for replying. Unfortunately, the postgres is read-only, so I can't create a new server. Thanks for the help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.