Editing data

The rows of a result can be changed in the grid: a cell’s value, new rows, rows to delete. Nothing is sent while you edit. The changes stay pending on the result until you look at the statements that apply them and say yes; then they run in one go, each row checked first.

What can be edited

The first key that changes a cell or a row reads the table’s details fresh (the status bar says “Loading the table’s details…”) and then does what was pressed. From then on the result tab strip says what the rows are to editing: editable · shop.users · key id (the key that finds a row), rows matched by values (no key in the result: a row is found by the values it shows), or read-only · <reason> for a reason about the statement, the table or finding the rows. The other reasons below, the connection’s among them, are said by the key only.

A result is editable when its statement is one SELECT of one plain table, its connection is open and may write, and its rows can be found again. A pressed key that cannot do its work says “Read-only: …” or “Can’t edit: …” and changes nothing. The reasons are:

• The statement. It is not a single SELECT; it uses WITH; it reads more than one table (a join or a comma in FROM); it has a subquery in the select list or in FROM; it combines results (UNION, INTERSECT, EXCEPT); it groups rows (GROUP BY, HAVING, DISTINCT, DISTINCT ON); it uses a window function; FROM names no plain table (a function, LATERAL, VALUES, a sample) or there is none; its columns do not come from the table named in FROM; it reads a view.
• The table. Its columns are not known yet (the keys are still being read, or could not be read), or it is not known at all (created outside the app: the explorer’s refresh reads it again); no column of the result is a column of the table; the rows are not of a plain table (a view, a materialised view or a foreign table); the table changed since the query ran; its details could not be read.
• Finding the rows. No column of the result compares exactly, so its rows can’t be found again (see How a row is found).
• A selection. Its cells are of more than one column (“Select cells of one column to edit them together”); it holds both new rows and rows read (“Select new rows or rows read, not both, to edit them together”); D on whole columns (“Select rows (V) to delete them, not whole columns”).
• The connection. The tab’s connection is not open; the connection’s environment is read-only (or was when the tab’s session opened); this connection type can’t edit data; the connection changed since these rows were read (run the query again).
• A column. It is computed in the query, not a column of the table; it is generated by the database; it is GENERATED ALWAYS AS IDENTITY (it takes no new value in a row read, but a new row may give one); it is shown more than once.
• A value. A row is not shown whole, so it can’t be found again; a column’s values (or one value) are not shown as stored, for example text read in another character set than utf8mb4 or a MySQL TIMESTAMP in a time zone that is not a fixed offset; a MySQL FLOAT column is shown rounded, so its selected cells are not typed together (one at a time is); a row could not be read; a row is marked for deletion; no column of the result takes a value in a new row.
• While applying or after. The pending changes are being applied; changes were applied to these rows (re-run the query to edit them); the table’s details are no longer known while changes are pending (run the query again).

Keys

In the grid:

The full lists are in the keybindings reference: the grid, the Space d group under nav, the cell editor, the editor of several lines, the Changes tab, the status line question and the Apply dialog.

While changes are pending the strip shows 3 pending and a Changes tab, and the hint line offers Space d a apply and u undo. A pending change is a row: a row read with changed cells (two cells of one row are one change), a new row or a row to be deleted. The row number’s gutter marks them ~, + and -, or ! when an Apply is refused for the row. Changed cells take their own colour, new rows another on a background of their own, rows to be deleted are crossed out, and a new row’s cell left to its default shows what the server will put there, or ! value needed where the column has none (see New rows). The theme tokens are edit_changed, edit_inserted, edit_inserted_row, edit_deleted, and edit_pending for the count (see Theme files).

With the mouse

A right click on a cell selects it and opens the action menu; inside a selected range it keeps the range, and the menu is the selection’s (Space Space opens the same one next to the selection). Its first section lists the edits after the copies, each with its key: edit the cell, set it to NULL or to the column’s default, add a row, add a copy of the row, mark the row to be deleted; once changes are pending, undo, showing the changes (the Changes tab) and dropping them. On a new row it lists only its edits: nothing there reads a row of the result. An item runs as its key does.

The menu offers the edits whose keys would act: each is checked as its key checks before it changes anything. An edit that would refuse is shown dimmed, with why in a few words where its key would be; the keys and the wheel step over it, and a click on it says the reason in full (as its key does) and keeps the menu open. A result that can’t be edited (a join, a read-only environment, a connection that is not open, a connection type that can’t edit data) shows that once, on “Edit the cell” (for example read-only, not connected, not editable), and offers no other edit but undo, the Changes tab and dropping the changes already pending (none of them while those are being applied). On a result that can be edited, an edit that does not act on this cell or selection is dimmed not here: for example setting NULL on a NOT NULL column, setting the default of a column that has none, editing cells of several columns at once, editing a column the server computes, deleting whole columns, or changing a cell of a row marked to be deleted. Adding a row to a table none of whose columns shown takes a value is dimmed no column to fill. Undo, the Changes tab and dropping the changes are listed only while changes are pending. Until the first edit key has read the table’s details, what its columns take is not known yet: the menu then offers NULL and DEFAULT on any column, and the key says why when one refuses it.

The cell editor

e opens a line over the cell with its pending value, or its value as read. A NULL or a pending DEFAULT opens as an empty line.

• Enter keeps the text. An empty text is the empty string, never NULL: Ctrl+N and Ctrl+D set NULL and DEFAULT again.
• Esc cancels; so does a click outside the line.
• Ctrl+L takes the text typed so far to the editor of several lines.
• With cells of one column selected, each takes the value, as one change (u undoes it).
• An enum or boolean column lists its values instead, then NULL and DEFAULT where the column takes them.

NULL is refused in a NOT NULL column. In a row read, DEFAULT is refused when the column has no default, and when its default would take a new number (an identity, AUTO_INCREMENT, a sequence): type a value instead. A new row takes any column’s default.

Values of several lines

A value with line breaks, and a JSON column’s, opens in a box with the query editor’s vim keys instead (Ctrl+L gets there from the line too). In Normal mode Enter or :w (:wq, :x) keeps the text, Esc or :q (:q!) cancels; / searches and :s substitutes as in the query editor. A value whose line breaks are all \r\n keeps them; one that mixes kinds of line break is refused (“mixes kinds of line break; it can’t be edited here”). The one-line editor does not take a paste with line breaks.

Value checks

A typed value must be a value of the column’s type, in the form the grid shows it; it is refused, with the reason, when it would be stored as something else. Some of the reasons:

• “not a whole number”, “out of the range of smallint”, “not a number”, “more digits than numeric(6,2) holds”
• “not true or false”, “not a UUID”, “not one of the column’s values”
• “not a date (YYYY-MM-DD)”, “not a date and time (YYYY-MM-DD HH:MM:SS)”, “more than 3 digits of a second” (a PostgreSQL value with a time zone needs its offset, such as +09)
• “longer than 20 characters”, “ends in spaces, which a CHAR column drops” (MySQL)
• “not bytes as shown: \x and hexadecimal digits in pairs” (MySQL: 0x and upper-case digits)
• “real would store it rounded, as …” (a floating-point value must read back as typed)

What the app cannot judge, such as a check constraint, is left to the server.

New rows

o adds a row with every value its column’s default. O copies the cursor’s row (its pending values, else as read), except what the table gives a new row itself: the key’s columns, a number drawn (an identity, AUTO_INCREMENT, a sequence), generated and computed columns are left to their default. A value that is not shown whole is not copied: the clone is refused, naming the column.

New rows appear at the end of the result, after the rows read, in the order they were added (on a new row, o and O put the next one right after it). The cursor goes to the new row. D on a new row removes it. A new row is tinted as a whole (theme token edit_inserted_row: its cells, the separators and its number), and the gutter numbers it after the rows read (4+, 5+ after three rows; rows an Apply added are numbered the same way). The number counts the rows read so far: when the next page is read, the new rows’ numbers move after it. The messages name a new row as the Changes tab does, by its place among the new rows (new 1).

A cell left to its column’s default shows, muted, what the server will put there:

This is only shown: the Apply leaves the cell out of the INSERT, so the server applies its own default, and the cell editor opens empty. The column widens to fit what it shows, up to the grid’s widest column. Copies of new rows are refused, so nothing shown here is ever copied.

The Changes tab lists, and an Apply refuses, a new row that leaves a NOT NULL column without a default to its default (“new 1 · name is NOT NULL and has no default; give it a value”), and one of a table with such a column that the result does not show (“…is not in the result; add it to the SELECT”).

The grid shows both where they are. A cell that needs a value reads ! value needed in the theme token edit_invalid (its column widens to fit it, up to the grid’s widest column), and the row’s gutter shows ! after its number instead of +. The cell editor opens saying “Can’t edit: name has no default”, and the inspector gives the Changes tab’s reason. A column the result does not show has no cell to mark: the row’s gutter shows !, and the Changes tab names the column. The badge counts both with the invalid values. Typing the values clears the marks.

Pasting a range

Cells copied from a spreadsheet, another grid or a text file paste into the grid with the terminal’s own paste (Cmd+V, Ctrl+Shift+V): put the cursor on the top-left cell the range goes to and paste. The terminal sends the text itself, so the app reads no clipboard: this works over SSH and in tmux, whatever clipboard is set to. The paste lands as pending changes, like typed values: marked in the grid, listed in the Changes tab, and nothing is sent until Apply.

The status line says how the text was read and what it did:

Pasted as TSV · 3 rows × 4 columns · 1 invalid (id=14 · price) · 5 cells changed · 1 new row · u undo

Where the status bar is too narrow for all of it, the counts of cells and new rows go first, then the invalid cell’s name: the format, the size, how many values are invalid and the undo key stay (Pasted as TSV · 3 rows × 4 columns · 1 invalid · u undo); at its narrowest the format goes too (Pasted · 3×4 · 1 invalid · u undo).

A first line whose fields are exactly the names of the columns the paste lands in, in order from the cursor’s column (a copy with its header line, from the app or a spreadsheet), is a header, not values: it is skipped, and the status line says so first (Pasted as TSV · Header row skipped · Space d V keeps it · …). Space d V reads the text again keeping that line, in the paste’s place. A single line is never skipped.

Formats

The text is read as:

• TSV when it has a tab outside quotes (a spreadsheet’s copy),
• else, when it has a comma outside quotes, CSV or one column (one value for a single line): which one is asked, never guessed (below),
• else one column, a value per line, when it has several lines,
• else one value.

Text with commas and no tab can be CSV, or a column whose values hold commas (1,5, Smith, John). The status line asks:

Read as CSV (3 columns) or one column? c / 1 · any other key cancels

c pastes it as CSV, 1 as one column (… or one value? for a single line). Esc, any other key, a click or another paste cancels (“Paste cancelled”) and does nothing else. The answer then pastes as any paste does: its refusals, its question about many rows and the status line naming the format. When only one of the two reads the text, only that key is offered: Not CSV (row 2 has 2 values, row 1 has 1); read as one column? 1. When neither does, the paste is refused with the CSV reason. The question is for the grid as it was: a change, another tab or a dialog drops it (“Paste cancelled: the rows changed”). It works the same for the terminal’s paste and for Space d v.

Both TSV and CSV take quoted fields ("a""b", line breaks inside quotes), as spreadsheets write cells that hold tabs, line breaks or quotes. A quote in the middle of a field that does not start with one is part of the text. Line ends may be \n or \r\n; a carriage return alone outside quotes is refused. Empty lines inside the block are rows of empty values. Every row must have as many values as the first: row 3 has 4 values, row 1 has 3 is refused, never padded or shifted.

When the text was read the wrong way, Space d V reads the last paste again as the next format, TSV → CSV → one value → TSV: it undoes the paste and pastes the same text again at the same cursor and selection. It works while that paste is the newest change, again after undoing a later one; otherwise it says “Nothing to re-read: the last change was not a paste”. When the new reading is refused, or its question about many rows is cancelled, the paste stays as it was and the next Space d V tries the format after that one. Text that can’t be read at all (a quote never closed, rows of different widths, a lone carriage return) names Space d V and the format it tries next.

Where it goes

Columns go by the grid’s column order from the cursor’s column; rows go down the grid’s rows: the rows read, then the new rows.

Rows past the last row become new rows at the end of the result, as o adds them: the columns outside the pasted range are left to their default, and the key’s columns, identities and AUTO_INCREMENT columns too unless the range sets them. A value equal to the cell’s value as read leaves the cell unchanged; if the cell had a pending edit, that edit is dropped. The cursor goes to the range’s first cell.

When a paste is refused

A paste that does not fit is refused as a whole, saying why, and nothing changes:

• wider than the columns from the cursor to the last one: “the paste is 5 columns wide; 3 columns from the cursor to the last column”
• a column in the range that takes no value (computed in the query, generated, an identity that is always generated, a column the result has twice)
• a row in the range marked for deletion: “row 12 is marked for deletion”
• rows past the last row read while the server has more rows: “the result has more rows on the server; fetch them (n) or paste fewer rows”. Otherwise rows meant to overwrite the rows not fetched yet would become new rows. When the result’s cursor is no longer open (the next page runs the query again, or there is none), it says “the result has more rows on the server, not read here; paste fewer rows”. One value filling a selection adds no row, so it is not refused for this.
• a row in the range that can’t be found again (its values are not shown whole)
• rows past the end where no row can be added: no column of the result takes a value in a new row, or the table has a NOT NULL column without a default that the result does not show
• a result that can’t be edited, or whose changes are being applied
• more than 5,000 rows or 1 MiB of text

In the Changes tab, or with the messages, a plan or a chart shown, a paste says “Paste into the grid”. With the cell editor open, a paste goes into the editor as before (the one-line editor takes no line breaks).

A paste that changes more than 1,000 rows (rows with a value set to another one, and new rows) asks first on the status line: Paste 2,400 rows (1,900 new)? y paste · any other key cancels. Any other key, a click or another paste cancels it and does nothing else.

NULL and empty values

An unquoted field that is exactly \N is NULL, the form both servers’ own dumps write NULL in. The setting edit.paste_null (:set edit.paste_null=NULL, or the settings screen) makes the word NULL the token instead, or none for no token. A quoted "\N" is always the text.

An empty field is an empty value, never NULL. For a text column that is the empty string; a column of another type does not take it, so it is marked invalid with “empty value (paste \N for NULL)”. A paste never gives DEFAULT: the text DEFAULT is the text.

Invalid values

Every pasted value is checked as a typed one is (see Value checks), and NULL against NOT NULL. Nothing is converted: a value its column does not take is kept, so you see it, and marked:

• the row’s gutter shows ! (over ~ and +), the cell is in the theme token edit_invalid, its value still shown, also under the cursor; the gutter’s ! also marks a new row an Apply is refused for (see New rows)
• the badge counts them: 5 pending · 1 invalid
• the Changes tab lists each under its row, with the reason: “price: not a number: ‘abc’” (elsewhere with the row named: “id=14 · price: not a number: ‘abc’”)
• the changes are not applied while any value is invalid (Apply shows the Changes tab instead)

Fix a value with e: the editor opens with the pasted text and says why it is not valid (“Not valid: not a number: ‘abc’”). Or drop it with Space d r, or undo the paste.

Undo

A paste is one change: u undoes all of it, the new rows too, and Ctrl+R redoes it.

Space d v and the clipboard setting

Space d v reads the system clipboard and pastes its text at the cursor, the same way. It reads the clipboard only when you press it, and only with clipboard = "auto" (outside SSH) or "system"; with clipboard = "osc52" (and auto over SSH) it says “Reading the clipboard needs clipboard = “system”; paste with your terminal instead“. A clipboard that does not answer within 2 seconds pastes nothing.

A terminal without bracketed paste sends a paste as typed keys instead: they act as keys in the grid. Use Space d v there, where a system clipboard exists.

Pasting the app’s own copies

The grid’s own TSV and CSV copies (Space r y) paste back as the same cells, with three exceptions: a text cell that is exactly the NULL token (\N by default) pastes back as NULL; NULL is copied as an empty field, so it pastes back as an empty value; and in a copy of one column, a last cell that is NULL (in a TSV copy, an empty text too) is lost, since the copy does not end its last line. To move cells within the app without these exceptions, use y and g p (below).

Copy and paste inside the grid

y (a cell, or the selected range) and Y (the row, or the selected rows) copy to the clipboard as before, and the app also keeps the cells themselves: each value exactly as the grid shows it, a pending change included, NULL as NULL, and never a header line. They stay, for this result or any other, in any tab, until you copy again or quit. No clipboard is read or written for them, so this works over SSH and in tmux, whatever clipboard and [editor] clipboard are set to. Two keys paste them:

• p (Space d Y) adds them as new rows after the last row, wherever the cursor is. Each p adds another copy, one step u undoes each.
  • Copied from this result’s table, each value goes to its own column, never by position: a column not copied is left to its default (one that needs a value is marked), a column the table computes or numbers itself (GENERATED, GENERATED ALWAYS AS IDENTITY) is left to the server (“… · 1 generated column left to the server”), and the paste is refused when a copied column is not in this result, is copied twice, or is computed or of another table (a join): it names the column.
  • Copied from another table, or from a result of computed columns only, the values go by position from the first column, each checked like a typed value, and a copy wider than the result is refused.
  • Y then p duplicates rows with their key values as they are: a serial or BY DEFAULT identity key then repeats, so set it to its default (Space d D) before Apply.
  • A copy from another connection or database goes by position.
• g p (Space d y) pastes them over the cells at the cursor, the way a pasted range does: from the cursor (one cell copied fills every selected cell), and rows past the last one are new rows.

Both are refused the way a paste is (a read-only result, more rows on the server, a table with a required column the result does not show, …), are one step u undoes, and ask first when more than 1,000 rows change. Nothing is read as text, so there is no question about commas, and Space d V has nothing to read again.

Pasted the copied cells as new rows · 2 rows × 2 columns · 2 new rows · u undo
Pasted the copied cells · 2 rows × 2 columns · 2 cells changed · 1 new row · u undo

Each value is checked against the column it lands in, as a typed one: a text pasted into a number column is kept and marked invalid, and the changes are not applied until it is fixed.

What is not kept says why, and the copy to the clipboard is done (or fails) anyway:

• more than 5,000 rows, the most a paste takes (“Not kept for p: more than 5,000 rows, the most a paste takes”);
• more than 32 MiB of values (“Not kept for p: more than 32 MiB of values”);
• a row not read whole;
• a MySQL value the grid does not show as stored: a FLOAT (the server sends it rounded), bytes or bits not in their form, text read in a character set other than utf8mb4, a TIMESTAMP read in a time zone without a fixed offset (one with daylight saving, or SYSTEM when the server’s own zone is not known), and text or a TIMESTAMP of a result whose session’s character set and time zone are not known, or changed while its rows were read.

A copy the grid refuses (a pending DEFAULT, a value only a run shows, a new row) or asks about keeps nothing either, so g p never pastes an older copy; with nothing kept, it says which keys copy.

A MySQL TIMESTAMP is shown in the session’s time zone, without an offset, so the same text is another instant in another zone. Its cells keep the fixed offset they were read in (SYSTEM is the server’s own zone), and g p pastes them into a time column with a zone only when the instant stays the same; else it says why and pastes nothing:

• a MySQL TIMESTAMP of a result read in another offset (“Not pasted: column “created_at” was copied in time zone +09:00; this result was read in +00:00”), in a zone without a fixed offset, in SYSTEM when the server’s own zone is not known, in a session whose zone is not known, or one whose zone changed while its rows were read: set the session’s time_zone to the copy’s and run the query again;
• a PostgreSQL timestamptz, which reads such text in its session’s zone.

A TIMESTAMP value pending in a result read in a zone without a fixed offset, in a zone not known, or in a session whose zone changed while its rows were read is not kept either. Into a column without a zone (DATETIME, timestamp, text) the text pastes as it was shown.

Undo, revert, discard

• u undoes the last change of the result and Ctrl+R redoes it. A change of several selected cells, a delete of several rows, a paste and a discard are one step each.
• Space d r drops the pending changes of the cell, or of the selection: a value goes back to the value as read, a row marked to be deleted is no longer marked, and a new row’s cell goes back to its default (a cloned row’s too, not to the copied value).
• Space d x (:discard) asks “Drop the pending changes?”: y drops them, n or Enter keeps them.

The Changes tab

While the shown result has pending changes, the result tab strip has a Changes tab after its result tabs: Changes 3, or Chg3 where the strip is narrow. A narrow strip keeps it while it fits next to the shown tab and Messages. Space d p (:changes) shows it with the focus; H and L (Space r [ and Space r ]) reach it as any result tab. It shows the changes of the result last shown in the grid. After an Apply it stays, as Applied (Appl), to show what was applied (see After an Apply). A plain Changes stays after the applied changes were rolled back, and after an Apply whose result is not known once its pending changes are dropped. It goes once nothing is pending and nothing was applied, or when the query runs again. When another result of the tab has pending changes too, a line says so: Result 2 also has 1 pending.

Its first line names the result and the table, and says how many updates, inserts, deletes and problems there are. Then one line per change, deletes first, then updates (a line per changed cell), then new rows, each row’s problems under it:

 Changes: Result 1 · shop.users               2 updates · 2 inserts · 1 delete · 1 problem
 - id=2   delete
 ~ id=1   email     'a@x' → 'z@x'
 ~ id=3   nickname  'cy' → NULL
 + new 1  email='n@x' · DEFAULT ×2
 + new 2  DEFAULT ×3
   ! email is NOT NULL and has no default; give it a value

• The marks are the gutter’s: ~ a changed cell, + a new row, - a row to be deleted, in their colours (edit_changed, edit_inserted, edit_deleted); they read without colours too.
• A row found by its key is named by the key’s values (id=2, a=1,b=2); a row found by its values by its row in the grid, row 31 (located by its values); a new row by its place among the new rows, new 1. A row is named the same way wherever a problem is about it: in the statements, the Apply’s messages, the cell editor and the inspector (new 1 · email is NOT NULL …).
• Values are literals as they were read and as they are to be ('text', NULL, DEFAULT), a long text cut at 40 characters with …. A new row lists the values set, then how many of its columns are left to their default (DEFAULT ×2, left out when none is); a column left to a default it lacks is named in the problem under the row. A line longer than the pane is cut with ….
• Control characters in values, column names and the table’s name are spelled out: \n, \r, \t, \x07 (\u{85} for one above \x7f), and so are the characters that turn the direction of text or are not seen (\u{202e}, \u{200b}). A value cut short is cut before an escape, never in it. A backslash is doubled where what follows it would read as one of these ('a\\nb' is the text a\nb), and shown as it is elsewhere ('a\b'). This is only how the list shows them: the statements, and what c copies, carry the values as they are.
• Every reason the changes can’t be applied yet is marked !, for every row (not only the first): under the row’s lines, without its name, for a new row’s NOT NULL column without a default, a row that matches no row or several rows; first, for what is about no row, such as a count that failed. A row that matches several identical rows is listed too: which of them to change is asked when you apply.

j and k select a line, Ctrl+D and Ctrl+U move half a page, g g and G go to the first and the last. Enter shows the line’s row and column in the grid (a row the grid no longer has is said). u and Ctrl+R undo and redo, and the Space d keys work as in the grid: an edit of a cell goes to the grid at the selected line’s cell first. Under the list the tab says how the Apply stands to a transaction (as the Apply dialog’s footer does), and its keys where they fit.

The SQL view

s switches between the list and the statements that apply the changes, for the rest of the run. ui.changes_view = "sql" (:set ui.changes_view=sql, or the settings screen) opens the tab in the statements; "list" is the default. The statements are shown as the Apply dialog shows them: in the order they run, in the editor’s syntax colours, each with the count it must give (expect 1) at the end of its first line, a statement longer than the pane wrapped under its first line.

On MySQL a text value is compared by its UTF-8 bytes, so the statement holds it as _utf8mb4 X'…'. A statement with such values is followed by a dim line that starts with ↳ and reads them back in order, column = 'text' (a long text cut with …; bytes that are not UTF-8 text say how many there are), grouped by where they are: set for the values an update writes, where for those a row is found by, values for those an insert adds. An update can name a column in both, its new value and the one it is found by:

  UPDATE `shop`.`people` SET `note` = _utf8mb4 X'615C62' WHERE  expect 1
    `id` <=> 1 AND `name` = _utf8mb4 X'706C61696E206E616D65' AND
    CONVERT(`name` USING utf8mb4) COLLATE utf8mb4_0900_bin <=> _utf8mb4
    X'706C61696E206E616D65' AND `note` = _utf8mb4 X'6F6C64' AND
    CONVERT(`note` USING utf8mb4) COLLATE utf8mb4_0900_bin <=> _utf8mb4
    X'6F6C64'
    ↳ set note = 'a\b' · where name = 'plain name', note = 'old'

That line is not SQL: it is never sent to the server, and c copies the statements exactly as they are sent, without it. A literal longer than a few hundred bytes is also shown shortened, its start and end around how many characters are left out (…786400 more characters…); it is copied and sent whole.

• Row checks (lock, no waiting) first: for every row read that changes, a SELECT … FOR UPDATE NOWAIT that finds and locks it. A row another session holds fails at once instead of waiting.
• Writes: the deletes, then the updates, then the inserts. A DELETE and a statement that expects more than one row are marked !, their count in the warning colour.

c copies every statement exactly as it is sent, each followed by ; and a line break (through the clipboard setting), in both views. While the statements are not final it copies nothing and says why: “Not copied: the SQL is not final yet: 2 problems”, or “…: the rows are still being counted”.

Rows found by their values are counted on the tab’s own session while the tab is shown, half a second after the last change, once for each state of the changes (“Counting matching rows…” until the answers come; the count sees the tab’s open transaction, as the Apply does). The list needs no count.

Apply

Space d a (:apply, or “Apply the pending changes (asked first)” in the action menu) works from the grid and from the Changes tab. The rows found by their values are counted afresh first, every time, whatever the Changes tab counted before; the count sees the tab’s open transaction, as the Apply does. The status bar says Checking 3 rows… until the answers come. Meanwhile any key (Esc too), a click, the wheel or a paste stops waiting (“Apply cancelled”) and does nothing else, so none of them is taken by the question that follows; a dialog that opens or a run in the tab cancels it too. Changes that move before the answers come drop the Apply: “Changes changed: press Space d a again”.

When the changes can’t be applied yet, nothing is asked: the Changes tab takes the focus with the first problem selected, and the status bar says “Can’t apply: 2 problems (see Changes)”. The problems are those of the list above, for instance:

• “row 3 · matches no row of the table now: it was changed or deleted. Re-run the query.”
• “row 3 · matches 2 rows; add the key to the SELECT to edit it”: the rows may differ in what the result does not show.
• “row 3 · email is not shown whole, so the row can’t be found again”, and the NOT NULL refusals of new rows above.

Otherwise the status bar asks, on one line in the warning color:

Apply 3 changes to shop.users (2 updates · 1 delete)?  y apply · any other key cancels

Only y applies. Any other key (Enter, Esc, n, any letter), a click or a paste cancels (“Apply cancelled”) and does nothing else: it never reaches the grid. Where the window is narrow the question leaves out the table, then the kinds but the deletes, and at its shortest is Apply 3 changes (1 delete)? y/n, the deletes always named. With manual commit the question also says how the Apply stands to a transaction (“Opens a transaction; nothing is committed.”, or “Runs inside the open transaction; nothing is committed.”); the shortest form says it too: Apply 3 changes (1 delete), nothing committed? y/n or …, in the open transaction? y/n. The question is for the changes as they were asked about: a change, another tab, a run, a transaction that begins or ends, or a lost connection drops it (“Apply cancelled: the changes changed”).

When a question on the status line (this one, a paste of many rows, CSV or one column) or the wait for the counts ends without its answer, cancelled by a key or dropped because something moved, the keys that answer questions (y, n, c, 1, on a Korean layout too) do nothing in the results for about a second, and say so: “Apply cancelled: the changes changed · y ignored”, or “Apply cancelled · y ignored”. A y pressed just as the question went never copies a cell over the clipboard. Other keys work as usual.

The Apply dialog

A dialog, “Apply changes: shop.users”, takes the place of the question when the statements must be seen before y:

• a row matches several identical rows: “row 3 · matches 2 identical rows”, with “[1] just one [a] all 2”. The table has no key, the result shows every column, each comparing exactly, and the rows are the same in all of them. 1 changes one of them, a all of them (the write then expects 2).
• the last Apply’s result is unknown (below): the first y warns, a second y applies again.
• the profile’s environment asks before every write (confirm = "writes"): the dialog is that question, no second one opens.

Cancel has the focus: only y applies. The dialog shows how many updates, inserts and deletes, then the statements as the SQL view shows them; j and k scroll line by line, c copies them, Enter, Esc or q closes. It closes by itself when its result, its rows’ connection or the changes change. Its footer says how the Apply stands to a transaction:

• “Runs in one transaction; commits at the end.” (automatic commit)
• “Opens a transaction; nothing is committed.” (manual commit, none open)
• “Runs inside the open transaction; nothing is committed.” (your own BEGIN, or a manual commit transaction already open)

How a row is found

A row read is found by the table’s primary key, or a NOT NULL unique key, when the result shows all its columns and each of them compares exactly; else by the values of its columns that compare exactly. A table others inherit from is always found by values. Floating-point numbers, JSON, XML, geometry and types the app does not know do not compare exactly, nor (PostgreSQL) arrays, intervals, a numeric without a scale, times with a time zone and network addresses, nor (MySQL) text in a character set that does not convert one to one. Values are compared byte for byte, never by the column’s collation. An update also checks that each changed column that compares exactly still holds the value read, and a delete every column that compares exactly: a row changed elsewhere in those columns since is not overwritten.

While it runs

y sends every statement on the tab’s own session. While it runs the strip (in the grid view) says applying…, the Changes tab says “Applying 3 changes…” over the list (or the statements) being sent, edit keys are refused, and the query’s cancel key (Ctrl+C) cancels it. When the result’s pages are held open, the cursor is closed first; the rows already read stay.

Either every statement gives its count and the changes are applied (“Applied 3 changes”), or nothing is (“Nothing was applied”, the changes stay pending and may be applied again). Why is said on the Changes tab’s second line, and in its SQL view the statement that failed is marked ! and scrolled to. A row is named as the Changes tab names it (id=5, row 3, new 1), and a statement in a message is its first line, cut short when long:

• “id=5 · was changed or deleted since it was read. Nothing was applied. Re-run the query to see the current values.”
• “row 3 · now matches 2 rows, not 1. Nothing was applied.”
• “id=5 · is locked by another session. Nothing was applied.”
• “new 1 · <the server’s error>. Nothing was applied. Statement: ”
• “Apply cancelled; nothing was applied.”
• “Apply result unknown: the connection was lost before the server confirmed. Re-run the query to check.” The changes may be in the table already: the next Apply opens the dialog, whose first y warns, and only a second y applies them again.

After an Apply

The result shows what the Apply did over the rows as read, until the query runs again: written values in italic (theme token edit_applied), deleted rows crossed out, the rows added after the rows read. The strip says applied · re-run to refresh (applied · not committed while its transaction is open; applied and in open tx where the strip is narrow). These rows are not edited again: run the query first.

The Changes tab stays as Applied, until the query runs again: its list is what was applied, its SQL view the statements that were sent (c still copies them), and its second line says “Applied · re-run to refresh”, or “Applied · not committed” while the transaction is open.

• PostgreSQL returns each written row as stored, so a value a trigger set shows too. A value in a form the grid shows differently (dates and times, floats, intervals, bytea, network addresses, most arrays) shows a dim re-run to see.
• MySQL returns no rows: a new row’s AUTO_INCREMENT column shows the number it took, other defaults stay default.
• Paging on past the rows already read is refused (“Changes were applied to these rows: re-run the query to see the rest”): the rows the server would skip have moved.

With manual commit, or inside your own transaction, the Apply is not committed (“Applied 3 changes; not committed yet”): the status bar counts it in the open transaction, and :tx lists it as “Apply (3 changes)”. A commit keeps the values shown; a rollback drops them (“The transaction was rolled back; the applied changes are gone”), and the Changes tab says so until the query runs again. See Manual commit.

Leaving with changes pending

Running a statement in the tab (its results are replaced), closing the tab and quitting ask first when changes are pending, saying how many: y drops them and goes on, n or Enter keeps them and does nothing. Changes already applied do not ask; an open transaction asks as usual. Deleting a connection profile or a saved query closes its tabs without another question: its own question says how many pending changes go (“2 pending changes of the results are dropped.”).

Copy and search

Copying (y, Y, the Space r y group, :copy), the search (/, ?), the cell viewer (Enter) and the inspector take the values the grid shows: a pending value or what an Apply wrote, not the value read. A copy with pending values says “(with pending changes)”, and counts the copied rows marked to be deleted, which are copied as shown.

• A pending DEFAULT has no value yet: a copy of it is refused, except as SQL INSERT, which writes DEFAULT there. A cell showing re-run to see is not copied, nor is one an Apply set to its default, shown default (“Not copied: nickname shows “default”; run the query again to copy its value”).
• New rows are not copied (“A new row is not copied: it is not a row of the result yet”) and the search does not look at them.

Export and the chart

An export to a file and the chart do not take the values the grid shows over the rows:

• An export of the rows held (the selection, the fetched rows, or every row of a result fetched to its end) is refused while the result has pending or applied changes (“Not exported: this result has pending changes; apply or discard them, or run the query again (see Changes)”; with changes applied, “Not exported: this result has pending or applied changes; apply or discard them, or run the query again”), in every format.
• An export of every row of a result the server has more of reads the rows again from the server, so it holds what an Apply wrote. With changes pending it goes on, and says “The pending changes are not in the file: every row is read again from the server”.
• The chart draws nothing while the result has pending or applied changes: it says “Not drawn: this result has pending changes. Apply or discard them, or run the query again (see Changes)”, or, once they are applied, “Not drawn: this result has pending or applied changes. Run the query again to chart the edited values.”