Extensions
Connecting
To make a PostgreSQL database accessible to DuckDB, use the `ATTACH` command with the `POSTGRES` or `POSTGRES_SCANNER` type.  
To connect to the `public` schema of the PostgreSQL instance running on localhost in read-write mode, run:  
```sql
ATTACH '' AS postgres_db (TYPE POSTGRES);
```  
To connect to the PostgreSQL instance with the given parameters in read-only mode, run:  
```sql
ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, READ_ONLY);
```  
By default, all schemas are attached. When working with large instances, it can be useful to only attach a specific schema. This can be accomplished using the `SCHEMA` command.  
```sql
ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, SCHEMA 'public');
```  
#### Configuration {#docs:extensions:postgres::configuration}  
The `ATTACH` command takes as input either a [`libpq` connection string](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)
or a [PostgreSQL URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS).  
Below are some example connection strings and commonly used parameters. A full list of available parameters can be found in the [PostgreSQL documentation](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS).  
```text
dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10
```  
| Name       | Description                          | Default      |
| ---------- | ------------------------------------ | ------------ |
| `dbname`   | Database name                        | [user]       |
| `host`     | Name of host to connect to           | `localhost`  |
| `hostaddr` | Host IP address                      | `localhost`  |
| `passfile` | Name of file passwords are stored in | `~/.pgpass`  |
| `password` | PostgreSQL password                  | (empty)      |
| `port`     | Port number                          | `5432`       |
| `user`     | PostgreSQL user name                 | current user |  
An example URI is `postgresql://username@hostname/dbname`.  
#### Configuring via Secrets {#docs:extensions:postgres::configuring-via-secrets}  
PostgreSQL connection information can also be specified with [secrets](https://duckdb.org/docs/configuration/secrets_manager). The following syntax can be used to create a secret.  
```sql
CREATE SECRET (
TYPE POSTGRES,
HOST '127.0.0.1',
PORT 5432,
DATABASE postgres,
USER 'postgres',
PASSWORD ''
);
```  
The information from the secret will be used when `ATTACH` is called. We can leave the Postgres connection string empty to use all of the information stored in the secret.  
```sql
ATTACH '' AS postgres_db (TYPE POSTGRES);
```  
We can use the Postgres connection string to override individual options. For example, to connect to a different database while still using the same credentials, we can override only the database name in the following manner.  
```sql
ATTACH 'dbname=my_other_db' AS postgres_db (TYPE POSTGRES);
```  
By default, created secrets are temporary. Secrets can be persisted using the [`CREATE PERSISTENT SECRET` command](#docs:configuration:secrets_manager::persistent-secrets). Persistent secrets can be used across sessions.