# PostgreSQL

Bobby Tables connects to PostgreSQL servers and opens each connection in a SQL workspace with a catalog sidebar, table grids, a structure editor and a SQL editor.

## Where to find it

| Action | How |
| --- | --- |
| Choose PostgreSQL | `New connection`, `⌘N` (`Ctrl+N`), then `PostgreSQL` under `Choose a provider` |
| Fill the form from a URL | `Use connection URL` with a `postgres://` or `postgresql://` URL, then `Fill fields` |
| Default port | `5432` |
The chooser lists PostgreSQL as a `Server database`. A `postgresql://` URL that uses port `26257` or a CockroachDB Cloud host fills the form as [CockroachDB](https://bobbytables.app/docs/cockroachdb) instead.

![The New connection dialog at Choose a provider, with PostgreSQL first among the database cards.](https://bobbytables.app/docs/img/connections/02-provider-chooser.png)

_PostgreSQL is the first server database in the chooser._

## Connecting

The form asks for `Host`, `Port`, `User`, `Database` and `Password`. `Test connection` reports the version string the server returns and the round trip, and refuses a CockroachDB server selected as PostgreSQL. The `TLS` and `SSH tunnel` sections set certificates and reach a private server, as described in [SSH and TLS](https://bobbytables.app/docs/ssh-and-tls). `Open in read-only mode` sets `default_transaction_read_only` on the session and disables editing in the interface.

![The connection form for Bookshop (PostgreSQL) with host, port, user and database filled in, a masked password, and a green Connected to PostgreSQL message with the server version above Test connection.](https://bobbytables.app/docs/img/connections/03-postgres-test.png)

_The test names the server version before anything is saved._

[A PostgreSQL connection URL is pasted into the URL box, Fill fields fills the host, port, user, database and password, and Test connection reports a successful connection.](https://bobbytables.app/docs/video/connections/04-url-test.mp4)

_A postgres URL fills the form in one step._

## What works

- Browse schemas, tables, views and rows in the [table grid](https://bobbytables.app/docs/table-data).
- Stage inserts, updates and deletes, then commit them in one transaction, as described in [Editing](https://bobbytables.app/docs/editing).
- Change columns, indexes and keys in the [Structure](https://bobbytables.app/docs/structure) tab, with the DDL run inside a transaction.
- Take a [snapshot](https://bobbytables.app/docs/snapshots) with the built-in engine or an installed `pg_dump`.
- Draw a [schema diagram](https://bobbytables.app/docs/schema-diagram) from declared foreign keys.
- Format result columns with [result directives](https://bobbytables.app/docs/result-directives).
- Stop a running statement with `Cancel`, `⌘.` (`Ctrl+.`), which cancels it out of band.
- Run `Explain`, `⌘⇧E` (`Ctrl+Shift+E`), or `Explain Analyze`, `⌘⇧⌥E` (`Ctrl+Shift+Alt+E`), in the [SQL editor](https://bobbytables.app/docs/sql-editor).

![The Bookshop (PostgreSQL) workspace with the bookshop schema in the sidebar and the orders table sorted by placed_at in the data grid.](https://bobbytables.app/docs/img/table-data/01-orders-sorted.png)

_A PostgreSQL connection opens in the SQL workspace._

## Engine notes

`Explain Analyze` runs the statement and requests timings and buffer counts. For anything other than a `SELECT`, the app asks `Run Explain Analyze?` first and rolls the statement back afterwards. Server notices appear with the results and can be dismissed. The catalog reads `pg_catalog` directly rather than `information_schema`.

![The results pane after Explain on a query against book_sales, listing the steps PostgreSQL would take as indented text with a Copy button.](https://bobbytables.app/docs/img/sql-editor/08-explain.png)

_PostgreSQL output from Explain opens as text you can copy._

## Good to know

- Structure changes run in one transaction, so a failed statement leaves the table as it was.
- Use `Switch database`, `⌘K` (`Ctrl+K`), to move to another database on the same server.
- A CockroachDB server is refused under the PostgreSQL provider. Choose [CockroachDB](https://bobbytables.app/docs/cockroachdb) for it.
- `LISTEN` notifications are not shown.

## Related

- [Connections](https://bobbytables.app/docs/connections.md)
- [SSH and TLS](https://bobbytables.app/docs/ssh-and-tls.md)
- [CockroachDB](https://bobbytables.app/docs/cockroachdb.md)
- [Engines, file sources and platforms](https://bobbytables.app/docs/engines.md)