Monghoul
Esc

    Edit rows in a SQL result

    Stage edits, inserts and deletes in a PostgreSQL, ClickHouse or SQLite result grid, review them as a table, and apply them.

    Checked against v2.0.0-rc.1 Updated
    A PostgreSQL result with staged edits: a new title, a value set to NULL, one set to DEFAULT, a deleted row and a new row, and a strip counting the changes
    Fig 01 Grid edits · Changes are staged in the grid, and nothing is written until Apply

    When a result can be edited

    A SQL result can be edited when it is a SELECT of one table that names each row by its key.

    • PostgreSQL: one table with plain columns or *, and only WHERE, ORDER BY, LIMIT and OFFSET. The table needs a primary key, and the result must hold every primary key column.
    • ClickHouse: one table of the MergeTree family, named by its sorting key, with no FINAL, SAMPLE, PREWHERE, GROUP BY, HAVING or LIMIT BY, and no row policy.
    • SQLite: one table, named by its primary key or by rowid.

    A cell that cannot be edited says why when you try, for example “Add every primary key column to the SELECT to edit the rows.” A result can be edited for 30 minutes, and a write in its database ends that.

    Stage changes

    Double-click a cell, press F2 or Enter, or start typing. The editor covers the cell, with a toolbar for the type (Value, NULL, DEFAULT), Cancel and Stage. Tab stages and moves to the next editable cell. Alt+Enter grows the field into a text box.

    Use the row menu for Delete Row and Insert Row. Staged changes are not written: a strip over the grid counts them.

    Review

    Click Review Changes. The changes show as one table: the row’s key first, a delete as a grey row, an updated cell with the new value and the old one small above it, and inserts as New row 1, New row 2. Discard on a row takes that change out. The SQL and its parameters fold under one heading, each statement with Copy. A review is valid for 60 seconds.

    Apply

    • PostgreSQL applies every change in one transaction. A row that changed since it was read stops the batch, and nothing is written. Reload rows reads the rows again and keeps your draft.
    • SQLite reads each row in full and checks it again before it writes, then applies the batch in one transaction.
    • ClickHouse has no transaction, so each stage commits on its own: deletes, then updates, then inserts. If a batch ends part of the way, each change says Applied, Not applied or Unknown.

    One review holds at most 100 changes on PostgreSQL and 50 on ClickHouse. On a write-protected connection, Apply asks first.

    Review Changes for SQLite: one statement for each change, the key bound last, all in one transaction
    Fig 02 Grid edits · Every change of the batch in one transaction