PostgreSQL.Database
Accessing DataReturns a navigation table of tables and views from a PostgreSQL database.
Syntax
PostgreSQL.Database(server as text, database as text, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
server | text | Yes | The PostgreSQL server hostname or IP address, optionally including a port number (e.g., "myserver.com" or "myserver.com:5433"). |
database | text | Yes | The name of the PostgreSQL database to connect to. |
options | record | No | An optional record with connection options such as CommandTimeout, CreateNavigationProperties, HierarchicalNavigation, and Query. |
Return Value
table — A navigation table of tables and views from the specified PostgreSQL database.
Remarks
PostgreSQL.Database connects to a PostgreSQL server and returns a navigation table of tables, views, and other objects from the specified database. The connector uses Npgsql (the open-source .NET driver for PostgreSQL), which is bundled with Power BI Desktop. No separate driver installation is required for Power BI Desktop. The function supports PostgreSQL on-premises, Amazon RDS, Amazon Aurora (PostgreSQL-compatible), Azure Database for PostgreSQL, and other managed PostgreSQL services.
Authentication: Database username and password authentication is the standard method. Configure credentials in the Power Query data source credentials dialog — do not embed passwords in the M query. Windows authentication (Kerberos) may be supported depending on your PostgreSQL server's pg_hba.conf configuration.
Schema navigation: PostgreSQL organizes tables into schemas (the default schema is public). The navigation table is organized by schema. Reference a table using the {[Schema="schema_name", Item="table_name"]} key record syntax. To group the navigation table visually by schema, set [HierarchicalNavigation = true] in the options record.
Query folding: PostgreSQL supports full query folding. Filters, projections, sorts, joins, groupings, and aggregations applied in Power Query are translated to SQL and executed on the PostgreSQL server. This is critical for performance on large tables — always apply filters before expanding columns.
Custom port: The default PostgreSQL port is 5432. To connect on a non-default port, append it to the server string: "myserver.com:5433".
Key options:
- CommandTimeout (duration) — maximum time to wait for a query to complete before timing out.
- CreateNavigationProperties (logical) — generate navigation properties from PostgreSQL foreign key constraints.
- HierarchicalNavigation (logical) — group the navigation table by schema.
- Query (text) — execute a native PostgreSQL SQL statement directly instead of navigating to a table. Useful for database-specific syntax (e.g., date_trunc, LATERAL joins, CTEs).
Power BI Service: Direct cloud refresh for PostgreSQL is not supported without an on-premises data gateway. Install the gateway on a machine that can reach the PostgreSQL server, and configure database credentials in the gateway data source settings.
Examples
Example 1: Connect to a PostgreSQL database
```powerquery
PostgreSQL.Database("myserver.com", "sales")Example 2: Connect to PostgreSQL on a non-default port
```powerquery
PostgreSQL.Database("myserver.com:5433", "analytics")Example 3: Navigate to a table in a specific schema
```powerquery
let
Source = PostgreSQL.Database("myserver.com", "sales"),
Orders = Source{[Schema="public", Item="orders"]}[Data]
in
OrdersExample 4: Run a native PostgreSQL SQL query using PostgreSQL-specific syntax
```powerquery
PostgreSQL.Database(
"myserver.com",
"analytics",
[Query = "SELECT date_trunc('month', created_at) AS month, SUM(revenue) AS total FROM orders GROUP BY 1 ORDER BY 1"]
)