How to Configure a SharePoint External Data Column for PostgreSQL
Question details
The user wants to populate a SharePoint list with data from a PostgreSQL data warehouse using an External Data column without relying heavily on Visual Studio.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating an External Data column to pull customer and complaint data from PostgreSQL when creating the required BDC model directly is difficult.
- Observed behavior
- SharePoint requires a BDC model and external content type, but native direct connectivity to PostgreSQL is not straightforward and often requires intermediate services or custom development.
Verify your SharePoint architecture (Online vs. On-Premises) as this determines whether you can use SharePoint Designer 2013 or need to build a custom intermediate API.
Use an Intermediate WCF or OData Service
Since SharePoint does not offer a native PostgreSQL connector out-of-the-box, wrapping your database in a supported intermediate service is the standard approach.
SharePoint Online provides built-in connectors for WCF and SQL Server Azure. To integrate PostgreSQL, you must first create an intermediate web service (OData or WCF) that queries your PostgreSQL database. Once hosted, SharePoint Designer can easily connect to this service.
Develop and deploy an OData or WCF REST service that securely connects to your PostgreSQL data warehouse and exposes the required customer and complaint records.
Launch SharePoint Designer 2013 and connect it to your target SharePoint site using your administrator credentials.
Navigate to 'External Content Types' in the left pane, click 'New External Content Type', and set your newly created OData or WCF service endpoint as the external system connection.
Go to your SharePoint list in the web browser, click 'Add Column', select 'External Data', and link it to the External Content Type you just created.
Consult the SharePoint Development Community for Custom Connectors
If creating an intermediate service is not feasible, specialized third-party connectors or custom API setups may be required.
Easily Analyze Exported SharePoint Data with WPS Office
If configuring complex BDC models for external data proves too tedious, you can export your PostgreSQL or SharePoint lists to CSV and analyze them seamlessly using WPS Office. It is a free, lightweight alternative offering excellent compatibility with Microsoft Office formats.
- 1. Export Your Data: Export your PostgreSQL query results or existing SharePoint list as a CSV or XLSX file to your local drive.
- 2. Open in WPS Spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open the downloaded database export file.
- 3. Analyze and Report: Use WPS Spreadsheet's built-in charts, data validation, and formulas to analyze your customer and complaint data locally.
Frequently Asked Questions
Can SharePoint Online natively connect directly to PostgreSQL?
No. SharePoint Online includes native connectors for WCF and Azure SQL. Connecting directly to PostgreSQL requires setting up an intermediate OData/WCF service or using a specialized third-party integration tool.
What is a Business Data Connectivity (BDC) model in SharePoint?
A BDC model is an XML-based file that defines the connection parameters, authentication, and data structures required for SharePoint to read and write data from an external system like a database or web service.
Do I need Visual Studio to create an External Content Type?
Not necessarily. While Visual Studio provides advanced coding capabilities for complex models, SharePoint Designer 2013 is a no-code tool that can easily generate BDC models and External Content Types for supported data connections.
