Table Editor

Use the Table Editor to manipulate the table data, and to perform other, associated tasks.

There are two tabs in the Table Editor:

Data.
On this tab, the table you are working with is shown.

DDL.
Initially, the CREATE TABLE statement used to create the table is shown on this tab.
You can edit the statement and then run it
( on the toolbar or Ctrl+Shift+F10).
Use or Ctrl+F5
to regenerate the CREATE TABLE statement for the current state of the table
in the database.

Use this icon, command or shortcut to delete the selected row or rows.

Rows are selected by clicking the cells in the column where the row numbers are shown.
To select more than one row,
use mouse clicks in combination with the Ctrl key.

Auto-commit

Use this check box
to turn the autocommit mode for the database connection on or off.

In the autocommit mode, each SQL statement is executed in its own transaction that is implicitly committed.
Consequently, the SQL statements executed in this mode cannot be rolled back.

(Each change of a value in the table, or adding or deleting a row -
when submitted to the database server -
results in executing an SQL statement.)

If the autocommit mode is off, transactions are committed or rolled back explicitly by means of
the commit or rollback command.
Each commit or rollback starts a new transaction which provides grouping
for a series of subsequent SQL statements.

In this case, the data manipulations in the transaction scope are committed or rolled back all at once
when the transaction is committed or rolled back.

Commit

If the autocommit mode is off,
use this icon
or command
to commit the current transaction.

Rollback

If the autocommit mode is off,
use this icon
or command
to roll back the current transaction.

Cancel Query

Ctrl+F2

Use this icon or shortcut to terminate execution of the current
query.

Data Extractor

Use this button or command to open a menu in which you can select an output format for your data.
The Configure CSV Formats command in that menu opens
the CSV Formats Dialog that lets you
manage the delimiter-separated values formats (e.g. CSV, TSV).

View Query

Use this button to view the query which was used to generate the
current table view.
To close the pane where the query is shown, press Escape.

This icon provides access to the following commands:

Transpose. Use this command to turn the transposed table view on or off.
(In the transposed view, the rows and columns are interchanged.
So, the rows are shown as columns and vice versa.)

Reset View.
Use this command to restore the initial table view after reordering or hiding the columns,
or sorting the data.

Sort via ORDER BY.
Use this command to turn the corresponding option on or off.

If the Sort via ORDER BY option is on,
all the sorting operations that you perform are reflected
in the corresponding SELECT statement
(an ORDER BY clause is added or modified)
which is executed immediately.
As a result, the data for the whole table is sorted by the corresponding database system.

Don't turn this option on if you want to keep interactions with the database to a minimum
(e.g. when the table is very big or the database connection is "slow").

If this option is off,
the data is sorted "locally" by IntelliJ IDEA and
only for the rows currently shown.

Row Filter.
Use this command to show or hide the box in which you can
specify filtering conditions for your table.

Settings.
Use this command to open the Database page of the Settings dialog
to view or edit the settings for
the Database, Hibernate and JPA consoles,
the Table Editor and the Database tool window.

Specify filtering conditions for the table.
(If the filter box is not currently shown, click on the toolbar and
select Row Filter.)

The filtering conditions are specified as in a WHERE clause
but without the word WHERE,
e. g. name LIKE 'a%' AND notes LIKE '%metal%'.
Within the LIKE expressions, the SQL wildcards can be used:
the percent sign (%) for zero or more characters and
underscore (_) for a single character.

To apply the conditions currently specified in the box, press Enter.
To cancel filtering, click , or
delete the contents of the filter box and press Enter.

To reapply a memorized filter, click and select the filter in the list.
See also,
Filter history size.

Clone Row

Ctrl+D

Use this command or shortcut to create a copy of the selected row.

Edit

F2

Use this command or shortcut to start editing a value in the selected cell or cells.
(Alternatively, you can double-click the cell or simply start typing.)

You can sort table data by any of the columns by clicking the cells in the header row.

Each cell in this row has a sorting marker in the right-hand part and, initially,
a cell may look something like this: .
The sorting marker in this case indicates that the data is not sorted by this column.

If you click the cell once, the data is sorted by the corresponding column in the ascending order.
This is indicated by the sorting marker appearance: .
The number to the right of the marker (1 on the picture) is the sorting level.
(You can sort by more than one column. In such cases, different columns will have different sorting levels.)

When you click the cell for the second time, the data is sorted in the descending order.
Here is how the sorting marker indicates this order: .

Finally, when you click the cell for the third time, the initial state is resorted.
That is, sorting by the corresponding column is canceled: .

Here is an example of a table where data are sorted by two of its columns.

To restore the initial "unsorted" state for the table,
click and select Reset View.
See also, Sort via ORDER BY.

To reorder columns, use drag-and-drop for the corresponding cells in the header row.
To restore the initial order of columns,
click and select Reset View.

To hide a column, right-click the corresponding header cell and select Hide column.

To show a hidden column:

Do one of the following:

Right-click any of the cells in the header row and select Column List.

Press Ctrl+F12.

In the list that appears, the names of hidden columns are shown struck through.