Studio edition /

Find and replace

Find text in every table and replace it cell by cell, after a preview, in one transaction you can undo. Studio edition.

On this page

Open it#

In the Studio edition, switch to Advanced and pick Find and replace in the sidebar. From the results of Search all tables, in advanced mode, Replace… opens it with the same term.

The term stays in the address: /advanced/replace?find=….

Search#

Field What it does
Find The text to look for.
Replace with The new text. It can be empty, to remove the match.
Match case Tells uppercase and lowercase apart. Off by default.
Whole word Only matches the term as a whole word, not inside another one.
Regular expression Takes the term as a PostgreSQL regular expression. In the replacement, \1 is the first group and \& the whole match. A wrong expression is reported before the search starts.
Schemas to search All schemas by default, or the ones you pick.

Without Regular expression, the term and the replacement are taken literally: ., * or \ mean themselves.

It searches text columns: text, varchar, char and other text types. Enums, arrays, JSON and generated columns are left out.

Preview#

Preview asks PostgreSQL for every matching cell, with its current value and the value it would get. Nothing changes yet. Tables are searched two at a time, with a progress count, and Stop ends the preview.

The result is grouped by table:

  • Each cell shows the change word by word: what goes out is struck through in red, what comes in is green, and the text around is trimmed.
  • Each cell, row and table has a checkbox, and Select every cell covers the whole result. Everything starts selected. The total updates as you untick: “342 cells in 5 tables selected”.
  • A table with more matches than the preview shows says More rows match than shown.
  • When a matching column is a primary key, a foreign key or has a unique index, the table warns you: “Changing a key or a unique column may break references or collide with another row.”

Some tables are listed apart, as left out, with the reason: no primary key (its rows can't be changed one by one), the role can't change it, or it took too long.

Replace#

Replace N cells asks for a confirmation with the number of cells and tables. Then:

  • Every chosen cell changes in one transaction.
  • If a row changed since the preview, nothing is written, as with any other conflict.
  • When it finishes, a notice says how many cells changed and offers Undo.

Undo restores the previous values, in one step for every table. Some replaces can't be undone:

  • More cells than GRESBASE_REPLACE_UNDO_LIMIT (5000 by default, from 0 to 50000). The confirmation warns you before, and asks you to type replace.
  • A table with triggers, or a change that cascades to other tables, as with any save in the grid. See Undo and redo.

Limits#

Limit Value
Rows per table in the preview 1,000
Rows in total in the preview 10,000. Past that the preview stops and says so.
Time per table 10 seconds
Cells that can be undone GRESBASE_REPLACE_UNDO_LIMIT, 5,000 by default

Permissions and read-only#

  • Only the columns your role can SELECT are searched, and only the ones it can UPDATE are changed. Row-level security applies, so you only see and change the rows your role can.
  • Every table of the preview is read in a read-only transaction with its own time limit.
  • In read-only mode, the preview works and shows what would change, without the button to replace.