Postgres Linked Server How To

Just a quick post showing how to add a Postgres database server as a Linked Server in Microsoft SQL Server.

  • Install the psqlODBC driver for Windows.
  • Control Panel > Administrative Tools > Data Sources (ODBC).
  • Click the "System DSN" tab and click the Add button.

new data source thumb Postgres Linked Server How To

  • Choose "PostreSQL ANSI" and click Finish.
  • Configure the data source by entering the server, username, password and database details.

Postgres ODBC Setup thumb Postgres Linked Server How To

  • Click "Test" to confirm the data source functions. If it fails ensure that Postgres is running and check your configuration details.
  • Click "Save" and then "OK" once successful.
  • Fire up SSMS and run the following T-SQL to create the Linked Server to Postgres.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRES', @srvproduct=N'Postgres', @provider=N'MSDASQL', @datasrc=N'PostgreSQL30'
 
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'POSTGRES', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'POSTGRES', @locallogin = NULL , @useself = N'False'
GO

Alternatively copy the below configuration in SSMS. The most critical thing here is to get the setting for "Data source" correct. This should be the name of the System DSN you added earlier.

Postgres Linked Server Configuration thumb Postgres Linked Server How To

  • Finally execute the query below. This uses OPENQUERY and will list all the tables in your Postgres system database.
SELECT *
FROM OPENQUERY(POSTGRES, 'SELECT * FROM INFORMATION_SCHEMA.TABLES');

If everything is running correctly you should see something like below.

postgres openquery result thumb Postgres Linked Server How To


One Comment

  1. Nick Heath says:

    Excellent guide thanks very much.

Leave a Reply

Current ye@r *