PostgreSQL
PostgreSQL is a relational database management system developed by PostgreSQL Global Development Group. Connect to on-premise PostgreSQL database to read data from tables.
This connector is available in the following products and regions:
| Service | Class | Regions |
|---|---|---|
| Logic Apps | Standard | All Logic Apps regions except the following: - US Department of Defense (DoD) |
| Power Automate | Premium | All Power Automate regions except the following: - US Government (GCC High) - US Department of Defense (DoD) |
| Power Apps | Premium | All Power Apps regions except the following: - US Government (GCC High) - US Department of Defense (DoD) |
| Contact | |
|---|---|
| Name | Microsoft |
| URL | Microsoft LogicApps Support Microsoft Power Automate Support Microsoft Power Apps Support |
| Connector Metadata | |
|---|---|
| Publisher | Microsoft |
| Website | https://www.postgresql.org/ |
| Privacy policy | https://www.postgresql.org/about/policies/privacy/ |
Prerequisites
PostgreSQL connector requires NPGSQL ADO.NET provider 4.0.10 to be installed. To install NPGSQL ADO.NET provider, go to release page and download the relevant release. The provider architecture (32-bit or 64-bit) needs to match the architecture of the product where you intent to use the connector. When installing, make sure that you select NpgSQL GAC Installation to ensure NpgSQL itself is added to your machine. The minimun supported PostgreSQL database version is version 9.4.
Known issues and limitations
The followings are some of the known limitations of using PostgreSQL connector:
- Insert row operation requires to provide explicit value for Primary Key column, even though default/autoincrement value is defined.
- Usage of the
Order Byparameter is recommended for Get rows action in order to get deterministic results in the output. IfOrder Byisn't specified, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on.
Creating a connection
The connector supports the following authentication types:
| Default | Parameters for creating connection. | All regions | Shareable |
Default
Applicable: All regions
Parameters for creating connection.
This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs
| Name | Type | Description | Required |
|---|---|---|---|
| Server | string | Specify the PostgreSQL server. Server[:port] | True |
| Database Name | string | Specify the PostgreSQL database name. | True |
| Authentication Type [Select Basic] | string | Authentication type to connect to your database | |
| Username | securestring | Username credential | True |
| Password | securestring | Password credential | True |
| Gateway | gatewaySetting | On-prem gateway (see https://docs.microsoft.com/data-integration/gateway for more details | |
| Encrypt Connection | bool | Encrypt Connection |
Throttling Limits
| Name | Calls | Renewal Period |
|---|---|---|
| API calls per connection | 300 | 60 seconds |
Actions
| Delete row |
This operation deletes a row from a table. |
| Get row |
This operation gets a row from a table. |
| Get rows |
This operation gets rows from a table. |
| Get tables |
This operation gets tables from a database. |
| Insert row |
This operation inserts a new row into a table. |
| Update row |
This operation updates an existing row in a table. |
Delete row
This operation deletes a row from a table.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Table name
|
table | True | string |
Name of table |
|
Row id
|
id | True | string |
Unique identifier of the row to delete |
Get row
This operation gets a row from a table.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Table name
|
table | True | string |
Name of PostgreSQL table |
|
Row id
|
id | True | string |
Unique identifier of the row to retrieve |
Returns
Get rows
This operation gets rows from a table.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Table name
|
table | True | string |
Name of PostgreSQL table |
|
Filter Query
|
$filter | string |
An ODATA filter query to restrict the entries returned (e.g. stringColumn eq 'string' OR numberColumn lt 123). |
|
|
Order By
|
$orderby | string |
An ODATA orderBy query for specifying the order of entries. |
|
|
Top Count
|
$top | integer |
Total number of entries to retrieve (default = all). |
|
|
Skip Count
|
$skip | integer |
The number of entries to skip (default = 0). |
|
|
Select Query
|
$select | string |
Specific fields to retrieve from entries (default = all). |
Returns
Get tables
Insert row
This operation inserts a new row into a table.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Table name
|
table | True | string |
Name of table |
|
Row
|
item | True | dynamic |
Row to insert into the specified table |
Returns
Update row
This operation updates an existing row in a table.
Parameters
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
|
Table name
|
table | True | string |
Name of table |
|
Row id
|
id | True | string |
Unique identifier of the row to update |
|
Row
|
item | True | dynamic |
Row with updated values |
Returns
Definitions
Table
Represents a table.
| Name | Path | Type | Description |
|---|---|---|---|
|
Name
|
Name | string |
The name of the table. The name is used at runtime. |
|
DisplayName
|
DisplayName | string |
The display name of the table. |
TablesList
Represents a list of tables.
| Name | Path | Type | Description |
|---|---|---|---|
|
value
|
value | array of Table |
List of Tables |