> ## Documentation Index
> Fetch the complete documentation index at: https://ngquct-docs-fix-500-query-results.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Connect to PostgreSQL 12 and later with the libpq driver, including PostGIS display, AWS IAM auth, and pg_dump backups

Unlike MySQL, PostgreSQL will not connect without a **Database**, and it changes database only by reconnecting. Everything else on the form is ordinary. The libpq driver ships inside the app and also serves [Amazon Redshift](/databases/redshift), [CockroachDB](/databases/cockroachdb), and [PGlite](/databases/pglite).

## Connection settings

| Field        | Default     | Notes                                       |
| ------------ | ----------- | ------------------------------------------- |
| **Host**     | `localhost` |                                             |
| **Port**     | `5432`      |                                             |
| **Username** | -           | Not pre-filled; most servers use `postgres` |
| **Database** | -           | **Required**                                |

Fill in the form and click **Save & Connect**. For remote servers use [SSH tunneling](/connections/ssh-tunneling); Google Cloud SQL instances can connect through the [Cloud SQL Auth Proxy](/connections/cloud-sql-proxy).

Support starts at 12. An older server still connects, with whatever its catalogs carry: generated columns need 12, declarative partitioning and identity columns need 10.

<Frame caption="PostgreSQL connection form">
  <img className="block dark:hidden" src="https://mintcdn.com/ngquct-docs-fix-500-query-results/HJY892UtvXUv1PFn/images/postgresql-connection-form.png?fit=max&auto=format&n=HJY892UtvXUv1PFn&q=85&s=a04676c15bf04210326e9cb0249f1ce4" alt="PostgreSQL connection form" width="1440" height="1224" data-path="images/postgresql-connection-form.png" />

  <img className="hidden dark:block" src="https://mintcdn.com/ngquct-docs-fix-500-query-results/HJY892UtvXUv1PFn/images/postgresql-connection-form-dark.png?fit=max&auto=format&n=HJY892UtvXUv1PFn&q=85&s=e72a6cba890b161291c677cebe9ea463" alt="PostgreSQL connection form" width="1440" height="1224" data-path="images/postgresql-connection-form-dark.png" />
</Frame>

## Connection URL

```text theme={null}
postgresql://user:password@host:5432/database
```

`postgres://` also works. Opening a URL connects directly. See [Connection URL Reference](/connections/urls).

## Authentication

### AWS IAM

Set **Authentication** to an AWS IAM option and set **Username** to a database role granted `rds_iam`. See [AWS IAM Authentication](/connections/aws-iam).

### Password file

Turn on **Use Password File** to read the password from `~/.pgpass` instead of typing one. Entries are `hostname:port:database:username:password`, `*` matches any field, first match wins. The form reports what it found, including `~/.pgpass has incorrect permissions (needs chmod 0600)`, the one that silently blocks a connect.

## Databases and schemas

Every database on the server is listed, `postgres` included; `template0` and `template1` are not. The sidebar shows every schema you have access to, and the toolbar carries the active database and schema side by side: click either to switch, or press `Cmd+K` for the database list.

Right-click a schema to drop it. The statement is `DROP SCHEMA … CASCADE`, so views and functions in *other* schemas that depend on it go too; the confirmation says so before it runs.

A partitioned table is listed once, under its own icon. Expand it for its partitions, and expand a partition again if it is subpartitioned; opening one works like opening any other table. Tables using the older `INHERITS` inheritance are listed normally, each child in its own right.

## Column types

`jsonb` renders as formatted JSON, and `uuid`, `inet`, `timestamp with time zone`, `interval` and `bytea` display natively. PostGIS `geometry` and `geography` render as EWKT with the SRID kept, `SRID=4326;POINT(-73 40.7237)`, rather than raw EWKB hex; a value that fails to convert stays hex.

An array column opens one of two editors, decided by its element type:

| Element type                                                                                             | Editor                                       |
| -------------------------------------------------------------------------------------------------------- | -------------------------------------------- |
| `text[]`, `integer[]`, `numeric[]`, `uuid[]`, `boolean[]`, `timestamptz[]`, enum arrays such as `mood[]` | A list, one row per element                  |
| `jsonb[]`, `bytea[]`, composite arrays, and any multi-dimensional array                                  | The plain text editor over the `{…}` literal |

In the list editor, reorder rows with the arrows, add and remove elements, and set a single element to NULL; an empty array and a NULL column stay distinct. Enum elements pick from the labels the type declares, and a label the type no longer lists stays selectable and is flagged. **Edit as Text** switches to the raw literal at any time.

## Cross-database tabs

PostgreSQL has no in-place `USE`, so a tab bound to a database other than the connection's active one runs on a second connection opened for that database. It shares no temp tables, session variables, or open transaction with the query editor on the main connection: keep a multi-statement transaction or a `CREATE TEMP TABLE` on tabs bound to one database. Binding itself is on [Tabs](/features/tabs#where-a-tab-points).

## Tools

`EXPLAIN` and `EXPLAIN ANALYZE` run with `FORMAT JSON` and render as a plan diagram or tree. See [EXPLAIN Visualization](/features/explain-visualization). **Database > Users & Roles** shows where each privilege comes from before you grant or revoke it. **Backup Dump** and **Restore Dump** shell out to your local `pg_dump` and `pg_restore`; see [Backup & Restore](/features/backup-restore).

## SSL/TLS

New connections default to **Preferred** (libpq `sslmode=prefer`): TLS first, plain text if the server refuses it. Pick **Verify CA** to validate the server certificate. See [SSL/TLS](/connections/ssl).

## Limitations

* Columns cannot be reordered. The structure editor adds, renames, retypes, and drops; changing the order of existing columns means recreating the table.
* A cross-database tab cannot share session state with the main connection. Statements that depend on a temp table or an open transaction have to run on one database.
* Backup and restore need `pg_dump` and `pg_restore` on your Mac. Neither is bundled; install them with Homebrew.

## Troubleshooting

**Connection refused**: check the server is running, that `listen_addresses` in `postgresql.conf` covers remote connections, and that the firewall allows port 5432.

**FATAL: password authentication failed for user "…"**: the role and password are checked against `pg_hba.conf`. Confirm the method on the matching line (`scram-sha-256` or `md5` for passwords, `trust` for local dev), and that the line matches the host you are connecting from.

**A Postgres-compatible engine loads no tables**: wire-compatible engines connect under the PostgreSQL type, and the catalogs they omit are probed for rather than assumed. An engine without `pg_matviews` still lists its tables; object kinds it does not expose will not appear at all.