PostgreSQl Integration with Office 365, SharePoint and more

PostgreSQL data can be integrated and synchronized codeless with various other data sources, on-premise or in the cloud, using the Layer2 Cloud Connector. Take a look here for supported systems and applications.

To connect to an PostgreSQL database, the data source entity must be configured as follows in the Layer2 Cloud Connector Connection Manager:

Fig. Example connection configuration to connect to an PostgreSQL database via the Npgsql provider.

PostgreSQL Integration And Synchronization Specific Settings

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.
• There is also an ODBC provider that works with the Cloud Connector. The official ODBC driver can be obtained here.
• Note that the provider architecture must match the Cloud Connector version installed (32- or 64-bit)
• Please select the "Npgsql Data Provider" 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;
  
  If the Password field exists in your version, place the password in that instead of in the Connection String. 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.
• You can make use of aliases in query to force auto-mapping in the Cloud Connector. See the PostgreSQL User Documentation.
• Bi-directional sync is generally supported. You query must be updatable for this (note that most joins are not). You will also need write access rights for this.

Do you have questions or issues to connect? Please contact [email protected] directly.

Step-by-Step Intros For Specific PostgreSQL Data Integration Scenarios

​You will find more information about specific scenarios here:

• How to connect and sync PostgreSQL data with SharePoint or Office 365

You are looking for other scenarios? Please contact [email protected] for any help.

PostgreSQL Data Integration - Known Issues and Workarounds

• ​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?