From the Database tool window (for any table within a DB data source):
- on the toolbar (if the toolbar is not currently hidden)
- Table Editor from the context menu
- F4F4F4F4F4F4F4F4F12 or Ctrl+Shift+ECommand Down or F4F4F3
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.
- Text. Shown initially on this tab is the CREATE TABLE statement used to create the table. You can edit the statement and then run it ( on the toolbar or Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Ctrl+Shift+F10Shift Command F10Shift Command F10Shift Command F10). Use or Ctrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RConsole.TableResult.ReloadCommand RConsole.TableResult.Reload to regenerate the CREATE TABLE statement for the current state of the table in the database.
Below, the features of the Data tab are discussed.
- Toolbar controls, context menu commands for data cells and keyboard shortcuts
- Using the table header row: sorting data, reordering and hiding columns
See also, Working with the Table Editor.
Toolbar controls, context menu commands for data cells and keyboard shortcuts
Most of the functions in the Table Editor are accessed by means of controls on the toolbar (on the Data tab), context menu commands for the data cells, and associated keyboard shortcuts.
|, , and||
These icons and corresponding commands are for switching between the result set pages,
i.e. the pages that show the
A fixed number of rows shown simultaneously is referred to as a result set page. If this number is less than the number of rows in the table, only a subset of all the rows is shown at a time.
In such cases, you can use , , and to switch between the subsets. (If all the rows are currently shown, these icons and the corresponding commands are inactive.)
The result set page size is set on the Database page of the Settings dialog.
|First Page||Use this icon or command to switch to the first of the result set pages to see the first series of rows.|
|Previous Page||Ctrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpCtrl+Alt+UpAlt Command UpAlt Command UpAlt Command Up||Use this icon, command or shortcut to switch to the previous result set page to see the previous series of rows.|
|Next Page||Ctrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownCtrl+Alt+DownAlt Command DownAlt Command DownAlt Command Down||Use this icon, command or shortcut to switch to the next result set page to see the next series of rows.|
|Last Page||Use this icon or command to switch to the last of the result set pages to see the last series of rows.|
|Reload Page||Ctrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RCtrl+RConsole.TableResult.ReloadCommand RConsole.TableResult.Reload||
Use this icon, command or shortcut to refresh the current table view.
Use this function to:
|Add New Row||Alt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+InsertAlt+Insert or Ctrl+NAlt+InsertCommand N or Control EnterControl N or Control EnterCommand N or Control Enter||
Use this icon, command or shortcut to add a new row to the table.
Complete entering a value into a cell by pressing EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or clicking another cell in the row.
|Delete Selected Rows||Ctrl+YCtrl+YCtrl+YCtrl+YCtrl+YCtrl+YCtrl+Shift+LCtrl+ECtrl+DCommand BackspaceCommand YCommand D||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.
|Settings||Use this icon 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.|
Use this button or command to open a menu in which you can select an active data extractor.
The Configure Extractors command in that menu opens
the Data Extractors dialog that lets you
manage the data extractors and their settings.
(A data extractor is a named set of settings that define how to convert table data into CSV, TSV and other formats.)
|/ Copy All To Clipboard / Save All To File||
Use this icon and/or associated commands:
|Save LOB||Use this icon or command to save the large object (LOB) currently selected in the table in a file.|
|Reset Columns Order and Visibility||Use this icon to restore the initial table view after reordering or hiding the columns, or sorting the data. As a result, the data, generally, becomes unsorted, the columns appear in the order they are defined in the database, and all the columns are shown.|
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 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.|
|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 EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape.|
|Sort via ORDER BY||
Use this icon to turn the Sort via ORDER BY option on or off.
If this 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.
|Show Filter||Use this icon to show or hide the box in which you can specify filtering conditions for your table.|
In this box, specify filtering conditions for the table.
(If the box is not currently shown, click on the toolbar to make it visible.)
The filtering conditions are specified as in a WHERE clause but without the word WHERE, e. g. name like '%y%' and relation <> 'sister'. Within the like expressions, the SQL wildcards can be used: the percent sign (%) for zero or more characters and underscore (_) for a single character.
|Edit Value||F2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt+EnterF2 or Alt EnterF2 or Alt EnterF2 or Alt Enter||
Use this command or shortcut to start editing a value in the selected table cell.
(Alternatively, you can double-click the cell.)
To save the modified value, press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or click a different cell. To cancel editing, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape.
|Go To | Row||Ctrl+GCtrl+GCtrl+GCtrl+GAlt+GCtrl+GCtrl+GCtrl+GCtrl+LCommand LCommand GCommand L||Use this command or shortcut to switch to a specified row. In the dialog that opens, specify the row number to go to.|
|Go To | Related Data||F4F4F4F4F4F4F4F4F12 or Ctrl+Shift+ECommand Down or F4F4F3||
Use this command or shortcut to switch to a related record.
The command options are a combination of those for
Go To | Referenced Data and
Go To | Referencing Data.
The command is not available if there are no related records.
|Go To | Referenced Data||Ctrl+B||
Use this command or shortcut to switch to a record that the current record references.
If more than one record is referenced, select the target record in the pop-up that appears.
The command is not available if there are no referenced records.
|Go To | Referencing Data||Alt+F7Shift+Alt+7Shift+Alt+7Alt+F7Alt+F7 or Shift+Alt+SAlt+F7 or Ctrl+Shift+EnterShift+Alt+F7Alt+F7Ctrl+GAlt F7Alt F7Shift Command G||
Use this command or shortcut to see the records that reference the current record.
In the pop-up that appears there are two categories for the target records:
The command is not available if there are no records that reference the current one.
|Filter by||Use this command to access quick filtering options. The options include those for the current column name and depend on the value in the current cell.|
|Ctrl+Q||Use this shortcut to open the Value view. Press Ctrl+Q for the second time to open the Transposed Row view. To close the view, press EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape. For more information, see Using the Value and the Transposed Row views.|
Using the table header row: sorting data, reordering and hiding columns
Use the cells in the header row (i.e. the row where column names are shown) for:
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, use . 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, use .
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+F12Ctrl+F12Ctrl+0Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12Ctrl+F12 or Ctrl+Shift+F12Ctrl+F3 or Ctrl+OCommand F12Command F12Command O.
In the list that appears, the names of hidden columns are shown struck through.
- Select (highlight) the column name of interest and press Console.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibilityConsole.TableResult.ColumnVisibility.
- Press EnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnterEnter or EscapeEscapeEscapeEscapeEscape, Escape or Ctrl+GEscapeEscapeEscapeEscapeEscapeEscapeEscape to close the list.
To show all the columns, use .