The databases world is expanding and I have been fortunate enough to learn and share my experiences around databases like MySQL, PostgreSQL apart from working with SQL Server. I always try to expand my horizon and try various database products. In case you don’t know, I have a course on Pluralsight about PostgreSQL.
It is always interesting to integrate various products and make them work seamlessly. Due to my knowledge of both database products, I have been asked one question very frequently.
How can I create linked servers in SQL connecting with Postgres?
Though this question looks simple and easy – I thought of writing a note to show you the actual steps to achieve the same. This blog shows a simple example about creating linked server. There is a provider called PGOLEDB which can be used for this purpose.
Create a Database and Table in PostgreSQL
- Create database:
CREATE DATABASE SQLAuthority WITH OWNER = postgres ENCODING = 'UTF8';
- Once database is created, change the connection, create table and insert some data.
CREATE TABLE MyTable ( ID integer NOT NULL, Name varchar(128) NOT NULL ); insert into MyTable values (1, 'Pinal Dave');
Verify that we have data in table
Select * from MyTable
Create a Linked Server in SQL Server
- Go to and choose “Download” from menu bar.
- Go to the page and choose “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” as shown below.
- Install it and then we should see provider it in SSMS
- Modify and run below script in SQL Server. You need to change Server Name, port etc.
-- Change parameter for provider Allow In Procees = true / DynamicParameters = true EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP' ,N'AllowInProcess',1 GO EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP' ,N'DynamicParameters',1 GO DECLARE @name NVARCHAR(4000); DECLARE @provider NVARCHAR(4000); DECLARE @servername NVARCHAR(4000); DECLARE @port NVARCHAR(4000); DECLARE @db_name NVARCHAR(4000) -- destination postgres database SET @name = N'SQLAuth_PG'; SET @provider = N'PGNP'; SET @servername = N'localhost'; SET @port = 'PORT=5432;' SET @db_name = N'sqlauthority'; -- create linked server EXEC MASTER.dbo.sp_addlinkedserver @server = @name ,@srvproduct = N'PGNP' ,@provider = N'PGNP' ,@datasrc = @servername ,@provstr = @port ,@catalog = @db_name -- username and password for postgres EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = @name ,@useself = N'False' ,@locallogin = NULL ,@rmtuser = N'postgres' ,@rmtpassword = 'sa' -- set up Extended properties of the Linked Server EXEC MASTER.dbo.sp_serveroption @server = @name ,@optname = 'data access' ,@optvalue = 'true' EXEC MASTER.dbo.sp_serveroption @server = @name ,@optname = 'use remote collation' ,@optvalue = 'true' EXEC MASTER.dbo.sp_serveroption @server = @name ,@optname = 'rpc' ,@optvalue = 'true' EXEC MASTER.dbo.sp_serveroption @server = @name ,@optname = 'rpc out' ,@optvalue = 'true' GO
- Once completed, we should be able to see linked server created as shown below.
Test the Linked Server
Here are the test queries which I have used based on earlier script.
-- USING FOUR PART NAMING SELECT [id],[name] FROM [SQLAuth_PG].[sqlauthority].[public].[mytable] GO -- USING OPENQUERY SELECT * FROM OPENQUERY(SQLAuth_PG, 'select id, name from mytable');
Have you ever created any other 3rd party linked server in your environments? What methods have you used? Will you be kind enough to share the same via comments?
Reference : Pinal Dave (https://blog.sqlauthority.com)
Thanks this really helped…
That’s great to hear Jonathan.
Will this work the official PostgreSQL ODBC Driver?
I have not tried. Please try and let us know.
The trial version is limited to 100 rows. In short, inquiries are limited.
PGNP is paid version any freeware available?
Very helpful, thank you. I only downloaded a trial version though, is the licensed version available for free somewhere pls?
I’ve followed the above steps but materialized views are not displayed – can anyone advise?
Select only 100 records