PGmacs

An Emacs-based browser and editor for the PostgreSQL database.

Overview gif

PGmacs provides an editing interface for the PostgreSQL 🐘 DBMS from Emacs. It allows you to:

browse the list of tables in the database
browse the contents of a table, row by row, in paginated mode for large tables
edit the value of a column (type RET on the value you want to modify)
delete a row (type DEL on the row you wish to delete)
copy/paste rows of a database table (type k to copy, y to paste in a table display buffer)
export the contents of a table to CSV or TSV format

PGmacs works with Emacs running in a terminal (started with the -nw commandline option), or running in your platform’s window system.

Supported versions

PGmacs is in beta status. Please only use it on test databases that do not contain important data.

Emacs version: PGmacs requires Emacs version 29. It has also been tested on the pre-release v30. It has mostly been tested on Linux, but should work as expected on Microsoft Windows and MacOS. It works both in graphical mode and in the terminal.

PostgreSQL version: PGmacs has been tested with PostgreSQL versions 16.3 and 17beta1, but should work with any PostgreSQL version supported by the pg-el library that it uses to communicate with PostgreSQL. For example, it works fine with PostgreSQL version 14 which was released in 2021.

PGmacs also works with some databases that implement the PostgreSQL frontend-backend protocol, but not with all of them. PGmacs queries various internal PostgreSQL tables for metainformation on the list of tables available, and these tables are not always present in PostgreSQL-compatible databases. PGmacs also uses some PostgreSQL-specific functions to display information such as the on-disk size of tables, and these functions are not always implemented. What we have tested so far:

ParadeDB v0.7.3 sees to work fine in limited testing (it’s more a PostgreSQL extension than a fully separate product).
YugabyteDB v2.21 works to a limited extent: we are not able to run the SQL command that adds a PRIMARY KEY to an existing table, nor to display total database size on disk, for example.
CrateDB v5.7 does not currently work; it does not implement PostgreSQL functions that we use to query table metainformation.
ClickHouse v24.5 does not work: its implementation of the wire protocol is very limited, with no support for the pg_type metadata.

License

PGmacs is distributed under the terms of the GNU General Public License, version 2.

Quickstart

In your Emacs initialization file, include the following to check out the latest version of the code from the git repository, as well as the dependency pg-el:

;; Requires Emacs 29 and git
(unless (package-installed-p 'pg)
   (package-vc-install "https://github.com/emarsden/pg-el" nil nil 'pg))
(unless (package-installed-p 'pgmacs)
   (package-vc-install "https://github.com/emarsden/pgmacs"))

(require 'pgmacs)

You can later upgrade PGmacs to the latest version with M-x package-vc-upgrade RET pgmacs RET.

With use-package: if you prefer the use-package macro (which is integrated with Emacs 29), you can instead say

(use-package pg :vc (:url "https://github.com/emarsden/pg-el"))
(use-package pgmacs :vc (:url "https://github.com/emarsden/pgmacs"))

Connecting to a PostgreSQL database

With the pgmacs.el library loaded, say

M-x pgmacs

This will open a widget-based buffer to collect connection information (database name, hostname, port, etc.). It reads initial values for these variables from the environment variables POSTGRES_DATABASE, POSTGRES_HOSTNAME, POSTGRES_PORT_NUMBER, PGPORT, POSTGRES_USER, POSTGRESQL_USERNAME, POSTGRES_PASSWORD and POSTGRESQL_PASSWORD, if they are defined (these semi-standardized variable names are used by the official Docker image for PostgreSQL and the more sophisticated Bitnami PostgreSQL image).

It will then open the PGmacs main buffer, which will show you a list of the tables available in the database.

You can also open PGmacs with a PostgreSQL connection string

M-x pgmacs-open-string RET user=myself port=5432 dbname=mydb

or with a PostgreSQL connection URI

M-x pgmacs-open-uri RET postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

or with a PostgreSQL connection object from the pg.el library, using function pgmacs-open.

Screenshot table list

The table list buffer

The table list buffer is the main PGmacs buffer. It shows some metainformation concerning the PostgreSQL backend that you are connected to (version, database size on disk, etc.), followed by a table which includes one row per table in the database.

The following keys are bound when the point is located in the table list buffer:

Key	Binding
`RET`	Open a new buffer to browse/edit the table at point.
`DEL`	Delete the table at point.
`r`	Rename the table at point.
`e`	Open a new buffer to display the result of an SQL query.
`<`	Move to the beginning of the table list.
`>`	Move to the end of the table list.
`{`	Make the current column narrower.
`}`	Make the current column wider.
`q`	Bury the current buffer.

Editing data in a PostgreSQL table

Screenshot table

A table buffer shows some metainformation on the table and its columns, then the row data in tabular form.

If the table contains a large number of rows, the contents will be paginated, with Next and Previous buttons to move page by page. The number of rows in each page is determined by the variable pgmacs-row-limit.

The following keys are bound when the point is located in the table:

Key	Binding
`v`	Display the value at point in a dedicated buffer.
`RET`	Edit the value at point in the minibuffer.
`w`	Edit the value at point in a widget-based buffer.
`DEL`	Delete the row at point.
`M-left`	Move to the previous column.
`M-right`	Move to the next column.
`+`	Insert a new row into the current table, prompting for new values in the minibuffer.
`i`	Insert a new row, prompting for new values in a dedicated buffer.
`k`	Copy the current row.
`y`	Paste (yank) the copied row.
`j`	Copy the current row to the kill ring in JSON format.
`<`	Move point to the first row of data.
`>`	Move point to the last row of data.
number	Move point to the nth column (numbering is zero-based).
`e`	Open a new buffer to display the result of an SQL query.
`r`	Redraw the table (does not refetch data from PostgreSQL).
`n`	Next page of output (if table contents are paginated).
`p`	Previous page of output (if table contents are paginated).
`S`	Sort the table by the current column.
`{`	Make the current column narrower.
`}`	Make the current column wider.
`q`	Kill the current buffer.

Editing a column value

If your table has a primary key, you can edit the contents of the table. To modify a value, move the cursor to the relevant column value and type RET. This will prompt you for the new value, and update the row to the value you specified (it sends PostgreSQL an SQL command similar to UPDATE table_name SET column_name to X WHERE pk_col1 = value1 AND pk_col2 = value2, where pk_col1 and pk_col2 are the names of the columns that comprise the primary key constraint).

Note that PGmacs tells you the column type when prompting for the new value. You must specify a value in the format accepted by PostgreSQL for that type (check the current value as displayed in the minibuffer if you’re unsure of the exepcted format).

For certain column types and very long column values, it may be more convenient to use the widget-based editing interface to edit a column value. Move the cursor to the relevant column value and type w. This will open a dedicated buffer with an editing widget suitable for that column’s type, as illustrated below for an HSTORE key->value map.

If you wish to abort editing, simply kill this editing buffer.

Inserting, copying and deleting rows

To insert a new row into a table, press + in the table buffer. You will be prompted for the values of each column for which a default value is not specified (in the minibuffer), then the new row will be inserted. You can also insert a new row by entering new values in a widget-based buffer by pressing i (this may be more convenient if the table contains many rows, or the values to enter are very long).

To delete the row at point, press <delete> or <backspace> in a table buffer and confirm. Please note that this deletes the current row in the PostgreSQL database, as well as in the Emacs buffer. Deleting rows is only possible on tables that include a primary key constraint (this can be a single column specified as PRIMARY KEY, or a constraint across a group of columns).

To copy/paste rows, press k to copy the row to the PGmacs kill buffer (this only copies, without deleting the row), then y to insert a new row with the same values. Any columns that have a default value specified (for example, primary key rows that pull a value from an integer sequence, or are specified as SERIAL, or timestap values that default to now) will be inserted with a new generated value, rather than the value in the copied row.

All updates, insertions and deletions are immediately made on the PostgreSQL server by sending it the appropriate SQL UPDATE TABLE, DELETE FROM or INSERT INTO commands.

Viewing output from an SQL query

You can also view (but obviously not edit!) the output from an SQL query you enter. Type e in a PGmacs buffer, which will prompt you for an SQL query, then display the output in a dedicated temporary buffer. Type q to kill the temporary buffer.

Screenshot table

Your feedback

Bug reports should be filed as issues on our GitHub project page.

Pull requests are also welcome!

Using PGmacs