postgres_fdw error could not connect server

Question

I wrote this PostgreSQL code lines, but I'm having the following error:

ERROR: could not connect to server "server\sqlexpress"

SQL state: 08001

What am I doing wrong?

My code:

USING PostgreSQL 9.3 on Windows 8.1 and MS SQL Express 2012 on same windows

  CREATE EXTENSION posgres_fdw;
  Query returned successfully with no result in 11 ms. 

  CREATE SERVER "server\SQLExpress" FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host 'localhost', dbname 'andesmar'); 
  Query returned successfully with no result in 12 ms.

  CREATE USER MAPPING FOR public SERVER "server\sqlexpress" 
    OPTIONS (user 'sa', password '1234'); 
  Query returned successfully with no result in 12 ms.

  CREATE FOREIGN TABLE datosplataforma (
     id                 bigint NOT NULL,
     messagedate        date NOT NULL,
     receiveddate       date NOT NULL,
     latitude           real,
     longitude          real,
     GPSspeed           real,
     bearingangle       integer,
     odometer           integer,
     eventtypeid        integer,
     vehicleid          integer,
     alertid            integer
   )
   SERVER "server\sqlexpress";
   Query returned successfully with no result in 12 ms.

   select * from datosplataforma; 
     ERROR: could not connect to server "server\sqlexpress"
     SQL state: 08001

Craig Ringer · Accepted Answer · 2014-05-07 02:13:32Z

4

postgres_fdw connects to another PostgreSQL server, not MS SQL Server.

If you want to connect to MS SQL, you need to use an ODBC or JDBC FDW with an MS SQL ODBC or JDBC driver, or to use a dedicated MS SQL FDW.

See: http://wiki.postgresql.org/wiki/Foreign_data_wrappers

answered May 7, 2014 at 2:13

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

Ale · Accepted Answer · 2023-05-26 16:02:30Z

0

I have met the same error too. If the server is the same as your current server, you should not mention the host and the port, maybe you need to code:

CREATE SERVER "server\SQLExpress" FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'andesmar');

The others are same.

edited May 26, 2023 at 16:02

Ale

1,03514 silver badges31 bronze badges

answered May 26, 2023 at 7:00

jhostman

1

Collectives™ on Stack Overflow

postgres_fdw error could not connect server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related