IntelliJ IDEA 2018.3 Help

Working with Query Results

When you run a query in the console, you receive results in the table format. Each statement appears in a separate tab in the Database console tool window. For example, if you have two SELECT statements in the query, you will see two tabs in the tool window.

Open a new tab for each query

By default, IntelliJ IDEA updates the same tab with results each time you run a query. You can change this behavior and create a tab each time you run a query.

  1. In the IDE settings (Ctrl+Alt+S), go to Database | General.

  2. Select the Open results in new tab checkbox and click OK.

Edit values in the result set

  1. In the result set, click a cell value that you want to edit.

  2. Specify a new value.

  3. To submit changes to a database, click the Submit icon (The Submit icon), or press Ctrl+Enter.

Export to a file

  • Right-click a, select Dump Data | To File.

  • Right-click a query, click Execute to File and select the file type that you want to use for export (for example, Comma-separated (CSV)).

  • On the toolbar, click the Dump Data icon (The Dump Data icon) and select To File.

Export a view to a file

Export to a clipboard

  • Select data in the result set or in the editor and press Ctrl+C.

  • On the toolbar, click the Dump Data icon (The Dump Data icon) and select To Clipboard.

Compare two result sets

  1. Select a result set.

  2. Click the Compare with icon (The Compare with icon), and select another result set.

  3. If needed, change a value of the Tolerance parameter in the comparison dialog. The Tolerance parameter defines a maximum number of differences that are allowed between two result sets. For example, if you want to consider two rows as equal if their data differs in a single column, enter 1 in the Tolerance field.

Hiding or showing the toolbar

To hide or show the toolbar of the Result pane and also that of the Database Console tool window:

  • Click icons general gearPlain svg on the title bar of the Database Console tool window and click Show Toolbar.

Pinning the Result tab

If one and the same tab is used to show your query results, and you get the result that you want to keep, you can pin the tab to the tool window. Do one of the following:

  • Right-click the tab and select Pin Tab.

  • Click Pin button on the toolbar.

    ShowResultsInNewTabOFFPinTab

See also, Show query results in new tab .

Depending on the value of the Limit page size to parameter, the result set might be divided into several pages. For example, you set the Limit page size to parameter to 100 but your query returned 200 rows. You will have two pages of rows, with 100 rows on each page. To navigate between pages, use the following controls:

  • The first page icon: Navigates to the first page of the result set.

  • The previous page icon: Navigates to the previous page of the result set. ( Ctrl+Alt+Up )

  • The next page icon: Navigates to the next page of the result set. ( Ctrl+Alt+Down )

  • Go to the last page icon: Navigates to the last page of the result set.

Making all rows visible simultaneously

If you want all the rows that satisfy the query to be shown simultaneously:

  1. Click icons general settings svg on the toolbar of the Database Console tool window.

  2. Switch to the Database | Data Views page, specify 0 in the Result set page size field, and click OK.

  3. Click icons actions refresh svg or press Ctrl+F5 to refresh the table view.

See also, Updating the table view and Result set page size.

Go to the specified row

To navigate to a row with a specified number, right-click the table and select Go To | Row (Ctrl+G). In the Go to row dialog, specify the row number and click OK.

Go to a specified row

Navigate to a related object

You can retrieve rows from the table for the selected object, and move the focus to the object. For example, select a field in the project tree and press Ctrl+Alt+B. The focus moves to the corresponding column in the new tab.

To navigate to the object in the result set, select an object and go to Navigate | Related Symbol (Ctrl+Alt+B).

Navigate between related data

You can navigate between different types of related data:

  • Referenced data: data that is referenced by the current object.

  • Referencing data: data that is referencing the current object.

Referenced and referencing data
  1. Right-click the column in the table and navigate to Go To | Referenced Data (Ctrl+B) or Go To | Referencing Data (Ctrl+Alt+B).

  2. If the Choose target pop-up window appears, select the table and what rows to show:

    • First Referencing Row: all rows in the table. The first row that references the current row is selected.

    • Referencing Rows Only: only rows that reference the current row.

  3. Press Enter

Sorting data

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:TableEditorHeaderCellUnsorted. 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:TableEditorHeaderCellSortAsc. 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:TableEditorHeaderCellSortDesc.

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

See also, Restoring the initial table view and Using the Structure view to sort data, and hide and show columns.

Reordering columns

To reorder columns, use drag-and-drop for the corresponding cells in the header row.

TableEditorReorderColumns

See also,Restoring the initial table view.

Hiding and showing columns

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

To show a hidden column:

  1. Do one of the following:
    • Right-click any of the cells in the header row and select Column List.

    • PressCtrl+F12.

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

    TableResultPaneHideColumn
  2. Select (highlight) the column name of interest and press Space.

  3. Press Enter or Escape to close the list.

See also, Restoring the initial table view and Using the Structure view to sort data, and hide and show columns.

Restoring the initial table view

Click IconDBToolbarMenu on the toolbar and select Reset View 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 corresponding query, and all the columns are shown.

Using the Structure view to sort data, and hide and show columns

When working with the Result pane, the table structure view is available as the corresponding popup.

The structure view shows the list of all the columns and lets you sort the data as well as hide and show the columns.

To open the structure popup, do one of the following:

  • Right-click a cell in the table header row and select Column List.

  • PressCtrl+F12.

In the popup, select the column of interest and do one of the following:

  • To sort the data by this column in the ascending order, pressShift+Alt+Up.

  • To sort the data in the descending order, pressShift+Alt+Down.

  • To cancel sorting by this column, pressCtrl+Shift+Alt+Backspace.

  • To hide the column (or show a hidden column), pressSpace. The names of hidden columns are shown struck through.

    TableResultPaneHideColumn

The shortcuts for sorting table data (Shift+Alt+Up, Shift+Alt+Down and Ctrl+Shift+Alt+Backspace) can be used in the Result pane without opening the structure view.

See also, You can sort table data by any of the columns by clicking the cells in the header row. , Hiding and showing columns and Restoring the initial table view.

Using the quick documentation view

The quick documentation view provides details about values in the selected cell or cells. For example, if a cell contains a long text, you will see the whole text. If a cell contains an image, you will see a preview of the image. Also, you can see records that are referenced in the current record as well as the records that reference the current one.

Object

Documentation view

Long text

If a cell contains a long text, you will see the whole text.

Quick documentation view for a long text

Image

If a cell contains an image, you will see a preview of the image.

Quick documentation view for a long text

Records

The quick documentation view for records displays records that are referenced in the current record and records that reference the current one.

Quick documentation view for an image

You can switch to the transposed view. In the transposed view, rows and columns are interchanged.

To switch to the transposed view, click Transposed View. For more information about the transposed view, see Transposing the table.

Transposed view for records

Transposing the table

The transposed table view is available. In this view, the rows and columns are interchanged.

To turn this view on or off, click IconDBToolbarMenu on the toolbar and select Transpose. Alternatively, use the Transpose context menu command.

Enabling coding assistance for a column

You can assign a column one of the supported languages (for example, SQL, HTML, or XML): right-click the corresponding header cell, select Edit As and select the language. As a result, you get coding assistance for the selected language in all the cells of the corresponding column.

Also, you can assign a language to an individual cell.

Selecting cells and ranges: using unobvious techniques

Adding cells with the same contents. Select a cell. Now, to add the nearest cell with the same contents to the selection, pressAlt+J. (When looking for the corresponding cell, IntelliJ IDEA moves down.) Each next press of Alt+J will add another cell to the selection.

To remove the cells from the selection one by one - starting from the last selected cell - useShift+Alt+J.

If a number of cells in the same row are initially selected, Alt+J and Shift+Alt+J work the same way.

Expanding a selection: cell - column - row - table. Select a cell. Now, to select all the cells in the current column, pressCtrl+W. The second press of Ctrl+W cancels the selection of the column and selects all the cells in the current row. Finally, the third press of Ctrl+W selects the whole table.

Ctrl+W works similarly if a number of cells or a range is initially selected.

Edit table cells

You can modify values in the table cells and, if appropriate, upload files.

Action

Shortcut

Description

Edit

F2

Edit a cell value. Alternatively, select a cell and start typing a new value. To confirm your changes, press Enter.

Edit Maximized

Shift+Enter

Expands a cell for editing. To confirm your changes, press Ctrl+Enter.

Add a new line

Ctrl+Enter

Adds a new line in the regular editing mode (F2).

Revert selected

Ctrl+Alt+Z

Restores the previous cell value.

Maximize

Ctrl+Shift+Alt+M

Expands the selected cell.

Add a new line in a maximized cell

Enter

Adds a new line in the maximized editing mode (Shift+Enter). To confirm your changes, press Ctrl+Enter.

Set the default value

Ctrl+Alt+D

Sets the current cell value to the default value.

Set the NULL value

Ctrl+Alt+N

Sets the current cell value to the NULL value.

Load file

Uploads a file into the field. Right-click a cell and select Load File.

Edit as

F2 or Shift+Enter

Edit a value in the cell as a fragment of SQL, HTML, XML, or any other supported language.

db modify cells edit as

Value completion

Ctrl+Space

Opens the suggestion list. To invoke value completion, start editing the cell and press Ctrl+Space. The list contains values from the current column that match your input.

Edit multiple cell values simultaneously

  1. Select a range of cells that you want to edit.

  2. Start typing a new value. The changes are applied to all the selected cells.

  3. To confirm the value, pressEnter. To cancel editing, pressEscape.

Add a row

  1. Click the Add New Row icon (The Add New Row icon) on the toolbar. Alternatively, right-click the table and select Add New Row from the context menu.

  2. PressAlt+Insert.

Delete a row

  1. Select the row or rows that you want to delete.

    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.

  2. Click the Delete Row icon the Delete Row icon on the toolbar. Alternatively, press Ctrl+Y or Delete.

Submitting changes

Submit changes manually

By default, changes in tables are not submitted automatically. At first, you make changes in your local copy of the table, then you need to submit all changes to a database.

To submit changes to a database, click the Submit icon (The Submit icon), or pressCtrl+Enter.

Submit changes automatically

By default, changes are not submitted to a database unless you press the Submit icon (The Submit icon), or pressCtrl+Enter. You can enable the Submit changes immediately option to submit your changes automatically.

  1. In settings (Ctrl+Alt+S), navigate to Database | Data Views.

  2. Select Submit changes immediately.

  3. Click OK.

Submit changes by using database transactions

You can select to commit transactions automatically or manually. To change the commit mode, use the Tx drop-down menu on the toolbar.

Change the transaction level

If you submit a change to the database server and the commit mode is set to Auto, each change of a value, row, or a column is implicitly committed and cannot be rolled back.

If the commit mode is set to Manual, you can explicitly commit or roll back submitted changes by means of the Commit icon (The Commit icon) or the Rollback icon (The Rollback icon). Otherwise, select Commit (Ctrl+Shift+Alt+Enter) or Rollback commands from the context menu.

The Tx switch can also be used for selecting the isolation level for the transactions.

Reverting changes

Revert changes

Before you submit the changes, you can revert them.

The scope of the revert command is defined by the current selection in a table: the command is applied only to the changes within the selection. So you can revert an individual change, a group of changes or all the changes. If nothing is currently selected, the revert command is applied to the whole table.

To revert non-submitted changes, select and right-click a cell or a number of cells. Select Revert Selected (Ctrl+Alt+Z).

Managing database transactions

You can select to commit transactions automatically or manually. To change the commit mode, use the Tx switch buttonTransactionControlDB on the toolbar.

TransactionControlDB

If the commit mode is set to Auto, each change of a value, or adding or deleting a row - when submitted to the database server - is implicitly committed and cannot be rolled back.

If the commit mode is set to Manual, the changes you have submitted to the server can be explicitly committed or rolled back by means of iconDBCommitTransaction or iconDBRollbackTransaction on the toolbar, or the Commit or the Rollback context menu command.

The Tx switch can also be used for selecting the isolation level for the transactions.

Comparing tables

You can compare the current table with any other table which is open in a data editor or shown in the Database Console tool window. To do that, click icons actions diff svg on the toolbar and select the table of interest.

The comparison results are shown in the differences viewer.

To compare contents of two or three cells within one table, select them and press Ctrl+Shift+D or select Compare Cells from the context menu.

DBCCellDiff

Copying table data to the clipboard or saving them in a file

When copying table data to the clipboard or saving them in a file, the data are converted into one of the available output formats. This can be SQL INSERT or UPDATE statements, TSV or CSV, an HTML table or JSON data. See Specifying data output format and options.

To copy or save the data, use:

  • Copy (available in the Edit and the context menu, the keyboard equivalent isCtrl+C). This command copies the data for the selected cells onto the clipboard.

  • Dump Data | To Clipboard (available in the context menu and can also be accessed by means of downloadArtifacts on the toolbar). This command copies the data for the whole table onto the clipboard.

  • Dump Data | To File (available in the context menu and can also be accessed by means of downloadArtifacts on the toolbar). This command saves the data for the whole table in a file. Before actually saving the data, the dialog is shown which lets you select the output format and see how your data will look in a file.

Copying and pasting data: data types are converted if necessary

You can copy (Ctrl+C) and paste (Ctrl+V) selected cells and ranges of cells - within the same table or from one table to another one. When pasting, IntelliJ IDEA converts data types automatically if and as necessary.

Specifying data output format and options

To specify the output format and options for the Copy and Dump Data commands (see Copying table data to the clipboard or saving them in a file), do one of the following:

  • Click DataExtractorButton on the toolbar.

  • Right-click the table and point to Data Extractor: <current_format>.

In the menu that opens, the output formats are in the upper part: SQL Inserts, SQL Updates, etc. (The options that look like file names are also the output formats or, to be more exact, the scripts that implement corresponding data converters.)

The output option are:

  • Allow Transposition. This option affects only delimiter-separated values formats (TSV, CSV). If the table is shown transposed and you are copying selected cells or rows to the clipboard (e.g.Ctrl+C), the selection is copied transposed (as shown) if the option is on and non-transposed (as in the original table) otherwise.

  • Skip Generated Columns (SQL). This is the option for SQL INSERTs and UPDATEs. When on, auto-increment fields are not included.

  • Add Table Definition (SQL). This is also the option for SQL INSERTs and UPDATEs. When on, the table definition (CREATE TABLE) is added.

Additionally:

  • Configure CSV Formats. This command opens the CSV Formats dialog that lets you manage your delimiter-separated values formats (e.g. CSV, TSV).

  • Go to Scripts Directory. This command lets you switch to the directory where the scripts that convert table data into various output formats are stored.

Exporting the data to another table, schema or database

You can export the data to another table, schema or database:

  1. Do one of the following:
    • Click iconExportToDB on the toolbar.

    • Select Export to Database from the context menu.

  2. Select the target schema (a new table will be created) or table (the data will be added to the selected table).

  3. In the dialog that opens, specify the data mapping info and the settings for the target table.

Saving a LOB in a file

If a cell contains a binary large object (a.k.a. BLOB or LOB), you can save such a LOB in a file.

  1. Right-click the cell that contains the LOB of interest and select Save LOB To File.

  2. In the dialog that opens, specify the name and location of the destination file and click OK.

Updating the table view

To refresh the table view, do one of the following:

  • Click icons actions refresh svg on the toolbar.

  • Right-click the table and select Reload Page from the context menu.

  • PressCtrl+F5.

Use this function to:

  • Synchronize the data shown with the actual contents of the database.

  • Apply the Result set page size setting after its change.

Viewing the query

To see the query that was used to generate the table:

  • Click View Query on the toolbar.

    If necessary, you can select the query text and copy it to the clipboard (Ctrl+C).

To close the pane where the query is shown, pressEscape.

Locate system query log

Last modified: 19 December 2018

See Also