Skip to content

Write and iterate on a query

You have a question for the database, and you want to ask it, look at the answer, change the query and ask again.

Open a query tab

Section titled “Open a query tab”

Press ⌘T on macOS, Ctrl+T on Windows and Linux, or run New query from the command palette. A query tab runs on the database you are connected to.

Run the statement you are on

Section titled “Run the statement you are on”

Put the cursor in a statement and press ⌘Enter on macOS, Ctrl+Enter on Windows and Linux, or click Run.

SELECT id, status FROM orders WHERE customer_id = 4471;


SELECT count(*) FROM order_items;

With the cursor on the first line, only the first statement runs. When the tab holds more than one statement, the one that runs flashes, so you can see which it was. A cursor after a semicolon, or on a blank line below a statement, runs the statement before it.

  • Select some text to run only the selection.
  • Press ⌘⇧Enter on macOS, Ctrl+Shift+Enter on Windows and Linux, to run the whole tab as a script, whatever is selected.

To make Run and ⌘Enter run the whole script instead, open Settings and choose Whole script under Run with nothing selected.

The result bar says how it went: the number of rows and the time, the command tag for a statement that returns no rows, or Failed on line and the line number. A script lists each statement’s outcome; click one to see its rows.

Cancel a statement

Section titled “Cancel a statement”

While a statement runs, Cancel takes Run’s place. Click it, or press ⌘. on macOS, Ctrl+. on Windows and Linux.

The result bar reads Canceled after and the time. The canceled statement changed nothing. In a script, the statements before it keep their results. See ALC-2001: Cancelled for what a cancel leaves behind inside a transaction.

Closing a query tab also stops its statement.

Try a change as a dry run

Section titled “Try a change as a dry run”

A dry run runs the statement or script inside a transaction, shows every result, then rolls it back. Use it to see what an UPDATE would touch before you keep it.

  1. Click the caret beside Run to open Run options.
  2. Choose Dry run. The button now reads Dry run, so you can’t mistake it for Run.
  3. Run as usual. The result bar says rolled back (dry run).

The caret beside Run, then Dry run.

Choose Run in the same menu to keep changes again. Toggle dry run in the command palette does the same. The choice stays with the tab.

If the tab already has a transaction open, the dry run runs inside a savepoint and rolls back to it, leaving your transaction as it was.

A statement that only reads, such as a plain SELECT, is offered no dry run, since there is nothing to roll back.

A dry run refuses, before anything runs, a statement Postgres won’t run inside a transaction, such as CREATE INDEX CONCURRENTLY or VACUUM:

Dry run refused: CREATE INDEX CONCURRENTLY cannot run inside a transaction, so a dry run cannot undo it. Nothing ran.

A rollback does not take everything back. These happen anyway:

  • Sequence values: nextval, and inserts into serial and identity columns, use up numbers.
  • Session advisory locks stay held.
  • Calls through dblink run in another session.
  • Files written by COPY … TO or lo_export stay written.
  • Backends ended by pg_terminate_backend stay ended.

Format a query

Section titled “Format a query”

Press ⌥⇧F on macOS, Alt+Shift+F on Windows and Linux, or run Format query. Alcuin lays out the whole tab with keywords in upper case and two-space indents. Text it can’t parse stays as you typed it.

Find a query you ran before

Section titled “Find a query you ran before”

Every run is kept in history, per connection.

  1. Press ⌘Y on macOS, Ctrl+Y on Windows and Linux, to open Query history in the bottom panel.
  2. Type in Search history to narrow the list. Each entry shows when it ran, how long it took and how many rows it returned.
  3. Click an entry to open it as a new query. Its Explain button explains it instead.

Yours shows what you ran; Agents shows what agents ran on this connection. Clear history deletes the entries in view, after asking.

Go to, ⌘P on macOS and Ctrl+P on Windows and Linux, also finds your 50 most recent history entries by their text.

Save a query

Section titled “Save a query”
  1. Press ⌘S on macOS, Ctrl+S on Windows and Linux, in the query tab.
  2. Name it. Alcuin suggests a name from the SQL, so Enter is often enough.
  3. If the connection belongs to a project, the query is listed on every environment of the project. Turn on This connection only to keep it to this connection.

Find it again in Go to, in Saved queries (⌘⇧B or Ctrl+Shift+B), on Home, or in the Queries group at the foot of the Explorer. Opening one that is already open goes to its tab.

When you change a saved query, its tab shows a dot and the editor’s top corner offers to save the changes. Save with ⌘S, or open the menu beside it for Save as new query… or Revert to saved. If you close the tab without saving, your edits stay as the query’s draft and come back when you open it again.

Work with .sql files

Section titled “Work with .sql files”
  • Open file… (⌘O or Ctrl+O) opens a .sql file in a query tab. ⌘S then saves to that file.
  • Save query as file… (⌘⇧S or Ctrl+Shift+S) writes the tab to a file.

If the file changes on disk, a tab with no unsaved changes reloads it. A tab with changes asks first. See ALC-3006: Conflicting change.

Large results

Section titled “Large results”

Alcuin reads only the start of a large result, then lets go of the database, so a result you leave open never holds locks. When it stops early, the result bar says so, such as Showing the first 100,000 rows. The rest weren't read., and Read the rest beside it reads the remaining rows. Results and memory gives the limits and when Read the rest is offered.

What success looks like

Section titled “What success looks like”

The result shows the rows you asked for, the query is saved or in history, and you can open it again in one step.

If it didn’t work

Section titled “If it didn’t work”