PostgreSQL Data Integration with SharePoint

PostgreSQL data sources can be integrated codeless with native SharePoint lists using the Layer2 Business Data List Connector. You can also connect to 100+ more supported systems and applications. In case you are looking for Online data integration, you will find the right tool here.​​​

Benefits of PostgreSQL Integration in SharePoint

• Very easy to setup in a few minutes: Create a SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below. That's it.
• No changes in the ODBC data source required: No programming, no additional tools.
• Connected list data always up-to-date: The connected PostgreSQL data query updates automatically in background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).
• One-way and optional two-way connection: You can write-back the changes made in the SharePoint list to the external PostgreSQL data source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for external systems.

• Well-known BCS "external list" issues and limitations are completely solved: ALL list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook.

• Workflows and notifications on external data change: List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when external PostgreSQL data records are changed.
• Application logging, reporting, and notifications: A SharePoint list is used to store settings and log information. SharePoint item versioning and workflows can be used to manage reporting and notifications. Direct notification per email in case of errors is supported as well.

• Highest Security, best performance, easy to maintain: SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance.
• 100+ external systems supported: Layer2 Data Providers included (e.g. for SharePoint/Office 365, Exchange, Dynamics, OData, XML/RSS, SOAP), vendor specific data providers can be used (e.g. SQL Server Oracle, mySQL etc.), 3rd party data providers also supported, e.g. for ERP/CRMs, Facebook or Twitter. See here for supported systems and applications.

PostgreSQL Specific SharePoint List Configuration Settings

​In the SharePoint General List Settings click "Connect to external data source". In the BDLC form, the data source must be configured as follows to connect to PostgreSQL using the Npgsql Data Provider.

Figure 1: Example connection configuration to connect a native SharePoint list to an PostgreSQL data source via Layer2 Business Data List Connector.

​Please note the following about specific settings:

• You will need to install a 3rd-party data provider to support PostgreSQL, such as the Npgsql .NET Data Provider. You can download and install from here.
• Note that the provider must be 64-bit to work with BDLC.
• Please select the ".NET Data Provider for PostgreSQL" from the list of installed providers.
• You can make use of standard connection strings for PostgreSQL, for example:
  Server=myServer; Database=myDatabase; User Id=myUser; Password=myPassword;
  
  Find more about PostgreSQL connection strings.
• Note that you may need to start the database or database server to validate. In some cases, a Port=; value will also be needed.
• You can make use of all SQL queries your data provider supports. See the PostgreSQL User Documentation.

• You can use the connection for uni- or bi-directional (write-back) synchronization. In case of inserts (full CRUD) via external systems, please take care that the primary key is defined and appropriate.

• There is also an ODBC provider that works with the BDLC. The official ODBC driver can be obtained here.

• No programming required for setup a connection and sync.
• No need to open your local network for access from outside.

Do you have questions or issues connecting? Please contact sales@layer2solutions.com directly.

Npgsql Provider Connection Details

​Provider:

NET Data Provider for PostgreSQL

Connection string sample:

Server=myServer; Database=myDatabase; Port=####; User Id=myUser; Password=myPassword;

Select Statement sample:
SELECT myField1, myField2 FROM myTable

For additional information, see the PostgreSQL User Documentation.

SharePoint Integration via PostgreSQL - Examples, Known Issues and Workarounds

​The integration of PostgreSQL-related data sources in SharePoint has the following known issues and workarounds:

• In case of inserts (full CRUD) via external systems please take care that there is a defined primary key and that it is appropriate.
• Please make use of the bdlcGUID column that will have a unique GUID assigned automatically, if required.
• ​If you are having issues getting the Npgsql Data Provider to work, try the ODBC driver instead. The official ODBC driver can be obtained here.
• There is a known issue where if column names start with a number, they will not be handled correctly. To get around this you will need to place the column name in quotes in the select statement, though it is recommended that you rename the columns so that they don't start with a number or special character.
• There is a known issue linked to the above issue with the ODBC driver that it will strip quotes out in the select statement if used on column names. This mostly affects syncing TO PostgreSQL, not pulling data from it. If this is happening, you will see an error like this:
  ERROR [42601] ERROR: syntax error at or near "a";Error while executing the query

To resolve this, you can use the Npgsql Data provider, which does not have this issue, or rename the columns so that the quotes are no longer required to get around column names not following PostgreSQL best-practices.

Ready to go next steps?