| title | PostgreSQL |
|---|---|
| description | Connect to PostgreSQL 9.1 and later with the libpq driver, including PostGIS display, AWS and Google Cloud IAM and Kerberos auth, and pg_dump backups |
import DeclaredColumnType from "/snippets/declared-column-type.mdx";
Unlike MySQL, PostgreSQL will not connect without a Database, and a connection to the server serves that one database. Everything else on the form is ordinary. The libpq driver ships inside the app and also serves Amazon Redshift, CockroachDB, and PGlite.
| Field | Default | Notes |
|---|---|---|
| Connect Using | Host and Port | Socket connects through the server's socket on this Mac. See Unix socket |
| Host | localhost |
|
| Port | 5432 |
With Socket, picks the socket file |
| Socket | empty | With Connect Using set to Socket: the folder holding the socket, or the socket file |
| Username | - | Not pre-filled; most servers use postgres. Empty signs in as your macOS login name |
| Database | - | Required |
Fill in the form and click Save & Connect. For remote servers use SSH tunneling; Google Cloud SQL instances can connect through the Cloud SQL Auth Proxy.
Any server from 9.1 on works. A feature the server predates stays out of the sidebar and the structure editor:
| Feature | Needs |
|---|---|
| Materialized views | 9.3 |
| BRIN indexes, the Bypass row level security role attribute | 9.5 |
| Declarative partitioning, identity columns | 10 |
| Generated columns | 12 |
A server on this Mac listens on a socket as well as a port, and a server with listen_addresses = '' listens only there. Set Connect Using to Socket and enter the folder that holds the socket. Host leaves the form, and Network offers only Direct.
The socket file is named after the port, .s.PGSQL.5432 for port 5432, so Port stays and picks the server. Entering the file itself, such as /tmp/.s.PGSQL.5433, saves its folder and sets Port from the name. The server lists its folders:
SHOW unix_socket_directories;| Where | Folder |
|---|---|
| Homebrew, Postgres.app | /tmp |
| Debian and Ubuntu packages | /var/run/postgresql |
The Socket field suggests both. The note under it names the socket file and says when nothing is there. Save still works, so a connection can be set up before its server starts. A path starting with ~ is expanded, and macOS caps the socket file's path at 103 bytes.
A local line in pg_hba.conf decides who signs in. With peer, leave Username empty to sign in as your macOS login name.
No SSL Mode applies: a socket connection never leaves this Mac, so the SSL settings leave the Network tab. Backup and restore reach the server through the same socket, and ~/.pgpass matches it the way psql does: the /tmp folder as localhost, any other folder by its path.
postgresql://user:password@host:5432/database
postgresql://user@localhost:5433/database?host=/var/run/postgresql
postgresql://user@%2Ftmp/database
postgres:// also works. A host starting with /, in host= or percent-encoded in place of the host, is the socket folder. Opening a URL connects directly. See Connection URL Reference.
Set Authentication to an AWS IAM option and set Username to a database role granted rds_iam. See AWS IAM Authentication.
For a Cloud SQL instance you reach directly, set Authentication to a Google Cloud IAM option and set Username to the IAM database user. See IAM sign-in without the proxy.
For a server whose pg_hba.conf line uses gss, set Authentication to Kerberos (GSSAPI) and set Username to the role the server maps your principal to. Leave Kerberos Principal and Password blank to use the ticket in your credential cache, or enter user@REALM.COM and your domain password to have one requested at connect. Get a cached ticket with kinit user@REALM.COM; klist shows it.
Connect by hostname, never an IP address: the ticket is for the service principal postgres/host.domain.com@REALM.COM, registered against the host name. Through an SSH tunnel the ticket is still for that host name. This Mac has to reach the realm's KDC, through DNS SRV records or /etc/krb5.conf.
GSS Encryption sets libpq's gssencmode: Preferred encrypts the session with Kerberos when the server allows it, Required refuses a server that does not, Disabled never tries. A session encrypted this way skips TLS, so SSL Mode applies only when GSS encryption is not used. Any other sign-in never tries GSS encryption.
Turn on Use ~/.pgpass to read the password from ~/.pgpass instead of typing one. Entries are hostname:port:database:username:password, * matches any field, first match wins. An empty Username matches your macOS login name, and a socket connection matches as described in Unix socket. The form reports what it found, including ~/.pgpass has incorrect permissions (needs chmod 0600), the one that silently blocks a connect.
Every database on the server is listed, postgres included; template0 and template1 are not. The sidebar shows every schema you have access to, and in each one the tables, views, materialized views and foreign tables you own or hold any privilege on, a column privilege included. To list fewer schemas, choose View > Filter Schemas; see Filtering schemas. The toolbar's centred control names the active database: click it, or press Cmd+K, for the list. The active schema has a picker at the foot of the object list in the flat layout, and Database > Schema everywhere.
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.
New Schema… and Edit Schema… on a schema row build CREATE SCHEMA … AUTHORIZATION, ALTER SCHEMA … OWNER TO, COMMENT ON SCHEMA, and the GRANT and REVOKE for USAGE and CREATE. The sheet shows them before it runs them. See Creating and editing a schema.
Owner lists the roles on the server; leave it at Database default to let the connecting role own it. Changing it needs you to be a member of the new owning role, and the server's own refusal appears under the form.
A grant to PUBLIC is a grant to every user on the server. A role literally named public is a different grantee and the grid keeps the two apart.
Rename from the same sheet, or in place: select the row and press Return.
pg_catalog, information_schema and pg_toast are refused for rename, edit and drop.
A partitioned table is listed once, with how many partitions it holds beside its name. Expand it for the partitions, each labelled with its bound: FROM ('2024-01-01') TO ('2024-02-01'), IN ('de', 'fr'), WITH (modulus 4, remainder 0), or DEFAULT. Expand a partition again if it is subpartitioned.
A partition is a relation, so opening, truncating, renaming and dropping one work as they do on any table. A partition declared in another schema, CREATE TABLE archive.orders_2023 PARTITION OF public.orders, is reached under its parent and carries archive into every statement built from it.
Tables using the older INHERITS inheritance are listed normally, each child on its own. Turn partitions off with Partitions under View Options.
That is what the Structure tab shows, what the MCP tools report as data_type, and what the AI context and autocomplete carry. An enum column keeps its value picker, and a domain column keeps the editor, formatting and sorting of the type underneath it.
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.
pgvector vector, halfvec and sparsevec cells show their first three elements and the dimension count, [0.0123, -0.0456, 0.0789, …] 1536 dims. Double-click one for every element beside its index, Copy as JSON, and Edit as Text for the literal. A value of the wrong length for its vector(1536) column stops the save and names the column.
An array column opens one of three 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[] and json[] |
The same list, with the selected element in the JSON viewer |
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.
A jsonb[] element gets the Text and Tree modes a jsonb cell gets, so the column reads the same browsing a table as it does through to_jsonb(col) in a query. Two things that query cannot show you: a SQL NULL element stays apart from a JSON null element, and reading an element does not rewrite it, so a json[] cell you open and save keeps the exact text it stored.
A value the list cannot represent opens the text editor instead: a multi-dimensional value such as {{1,2},{3,4}}, or a literal carrying explicit bounds such as [0:2]={a,b,c}. Edit the {…} literal directly.
box[] is the one column whose elements are not separated by a comma. PostgreSQL uses a semicolon there, because a box's own text contains commas, and the list reads it that way.
Each schema lists its sequences under Sequences, with the owning column on hover. Set Next Value… realigns a sequence with the highest value in its column, and Edit Sequence… changes its increment, bounds, cache and cycling. See Sequences.
Enums, composites, domains and ranges are listed under Types in each schema, where New Type… creates one and a type's tab edits it. The structure editor's type picker offers the schema's types under User-Defined. See User-Defined Types.
A view's definition is rebuilt to run anywhere. Every table it reads is schema-qualified, and WITH (security_barrier), WITH (security_invoker) and WITH CASCADED CHECK OPTION are written back, so executing the statement elsewhere keeps the restrictions the original carried. Read it with Show DDL, copy it with Copy DDL, or edit it with Edit View Definition. A materialized view's statement carries its access method, storage parameters and tablespace, and the DDL tab adds its indexes.
REFRESH MATERIALIZED VIEW runs from Refresh Materialized View…. A plain refresh holds an ACCESS EXCLUSIVE lock, so nothing reads the view until it finishes. Refresh concurrently takes EXCLUSIVE instead and leaves readers working, and PostgreSQL accepts it only for a populated view with a valid unique index on plain columns, no WHERE clause and no expressions; a partial or expression index does not qualify. The refresh runs on its own connection, outside any transaction a query editor holds open. Refreshing needs ownership of the view, or the MAINTAIN privilege from PostgreSQL 17.
Edit Comment… writes COMMENT ON, picking TABLE, VIEW, MATERIALIZED VIEW or FOREIGN TABLE to match the object. Column comments are written from the Columns section as COMMENT ON COLUMN. Clearing the field writes IS NULL, which removes the comment. Only the object's owner may comment on it.
An SQL export writes those statements back for tables, views and materialized views, so a dump and a restore keep their comments. See Import & Export.
The Structure tab of a view, a materialized view or a foreign table offers only the edits PostgreSQL takes on that kind of object, which is fewer than a table's and different for each. See Table Structure.
PostgreSQL refuses INSERT, UPDATE and DELETE on a materialized view, so its rows open read-only in the data grid. Edit the tables it reads from, then choose Refresh Materialized View….
A CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY that fails or is cancelled leaves an invalid index on the table. The Indexes section names it, and exports leave it out as pg_dump does. Table Structure covers dropping or rebuilding it.
PostgreSQL has no in-place USE, so every database browsed from the connections strip gets a server connection of its own. Moving between two database entries switches between those connections without reconnecting, and each keeps its open transaction, temp tables, and SET values until its entry is closed or the connection ends. Closing the entry or the connection, or disconnecting, asks first when that rolls back an open transaction. A tab runs on the connection of the database it is bound to.
A tab bound to a database that has no entry runs on a metadata connection instead. That one is shared with the Structure tab and closes after 10 minutes idle, so open the database from the strip before starting a transaction or a CREATE TEMP TABLE there. A database connection that died while idle is reopened on its next use; if it held a transaction, that statement is refused once with a message saying the server rolled it back. Binding itself is on Tabs.
In pg_stat_activity, each database's own connection reports application_name as TablePro, one per database entry in the strip. The metadata connections that read the sidebar and the Structure tab, and the ones a tab on a database without an entry runs on, report TablePro Metadata. To name them yourself, put -c application_name=reporting in Connection Options: every connection then uses that name.
Sessions run in UTF8 whatever encoding the database was created with, and the server converts text both ways. Japanese in an EUC_JP database or accented names in a LATIN1 one read and save intact, and stay that way after RESET ALL or DISCARD ALL in a query tab. A -c client_encoding=… in Connection Options is overridden; other settings there still apply.
EXPLAIN and EXPLAIN ANALYZE run with FORMAT JSON and render as a plan diagram or tree. See 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. The Routine Debugger steps through PL/pgSQL functions and procedures once the server has the pldbgapi extension. A Live tab runs LISTEN on a session of its own and shows each NOTIFY as it arrives.
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.
- Columns cannot be reordered. The structure editor adds, renames, retypes, and drops; changing the order of existing columns means recreating the table.
- Session state belongs to one database. A temp table or an open transaction made on
appis not visible from a tab onlogs, and closing theappentry ends it, after asking first when a transaction is open. - Backup and restore need
pg_dumpandpg_restoreon your Mac. Neither is bundled; install them with Homebrew.pg_dump15 and later refuse PostgreSQL 9.1, so back one up withpg_dump14 or earlier. - With Kerberos, backup and restore use the ticket in your credential cache, never one requested from Kerberos Principal and Password. Run
kinitfirst. - Before PostgreSQL 12, the
run_maintenancetool'sREINDEXwith notableruns withoutCONCURRENTLYand rebuilds the system catalog indexes as well. While an index rebuilds, writes to its table wait, and so do reads that use that index. Run it when the database is quiet.
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.
No Kerberos ticket was found.: the credential cache holds no ticket. Run kinit user@REALM.COM, or fill in Kerberos Principal and Password, then reconnect. The Kerberos ticket has expired. asks for the same.
The PostgreSQL Kerberos service principal is not registered.: the KDC does not know postgres/host.domain.com. Ask your administrator to register it in the realm, and connect by the host name it is registered under. macOS keeps that answer for up to 20 minutes, so a reconnect right after the principal is registered can still fail; quitting TablePro clears it.
This server signs in with Kerberos.: the server's pg_hba.conf line uses gss, and Authentication is set to something else. Set it to Kerberos (GSSAPI).
ERROR: invalid byte sequence for encoding "UTF8": 0x…: the database's encoding is SQL_ASCII, which stores bytes without checking them, and a value in the result is not UTF-8. Run SHOW server_encoding to confirm. Move the data to a UTF8 database: dump it with pg_dump --encoding= and the encoding the text was written in, such as EUC_JP or WIN1252, then restore that dump into a database created with ENCODING 'UTF8'.
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. One missing a catalog, function or column the listing reads loses table comments first, then falls back to information_schema.tables: tables and views appear, each partition as a table of its own, and materialized views and foreign tables do not.