PostgreSQL.Database
A PostgreSQL database's tables and views to navigate, or the rows of a query in PostgreSQL's SQL.
Group: Databases and lakes. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
PostgreSQL.Database(server as text, database as text, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
server | text | The host, or host:port. |
database | text | The database's name. |
options | nullable record optional | Query: a SQL statement whose rows are the result. |
Returns
table. Without Query, a navigation table (Name, Data, Schema, Item, Kind) of the tables and views, information_schema and pg_catalog left out; with Query, the statement's rows.
Remarks
- DuckDB reads PostgreSQL itself, so a table's Data is read lazily: the steps after it fold into DuckDB's SQL, which reads only the columns and rows it needs.
- Sign in as a database user, with a user name and password; Get data's Encryption field is PostgreSQL's SSL mode.
- Credentials are never part of the M: the studio asks for a sign-in the first time this computer reads the source and keeps it on this computer only, for you.
Compared with Power Query
Of Power Query's options only Query is read; the others are accepted and change nothing.
Examples
A table of the public schema
PostgreSQL.Database("pg.norwayanalytics.no", "shop"){[Schema = "public", Item = "orders"]}[Data]Result: not shown here: it needs a PostgreSQL server and a sign-in.
A query in PostgreSQL's SQL
PostgreSQL.Database("pg.norwayanalytics.no:5432", "shop", [Query = "SELECT shop, date_trunc('month', ordered) AS month, sum(amount) AS sales FROM orders GROUP BY 1, 2"])Result: not shown here: it needs a PostgreSQL server and a sign-in.