Writing and Executing SQL Statements
Use the input pane of the database console to compose and execute your SQL statements, and also to perform other, associated tasks. (The input pane is shown as a tab in the editor.)
- Selecting the default schema or database
- Controlling the schema search path for PostgreSQL
- Composing SQL statements
- Editing data for INSERT statements in table format
- Navigating to a table or column view in the Database tool window
- Configuring the Execute command
- Executing an SQL statement
- Executing parameterized statements
- Executing a group of statements
- Executing all statements
- Executing a part of a statement (e.g. a subquery)
- Executing auto-memorized statements
- Outputting the result of a SELECT statement into a file
- Using the error notification bar
- Canceling running statements
- Managing database transactions
- Showing execution plans
- Showing DBMS_OUTPUT for Oracle
Selecting the default schema or database
You can select the default schema or database by using the list in the right-hand part of the toolbar. If you do so, you'll be able to omit the name of that schema or database in your statements.
Note that you cannot switch schemas like this for read-only MySQL data sources, see Read-only.
Controlling the schema search path for PostgreSQL
When working with a PostgreSQL data source, the default search path (one set in the database) is used unless you specify a different search path.
To control the search path, use the popup in the right-hand part of the toolbar.
If the search path should include only one schema, open the popup and click the necessary schema. In the same way you can replace a schema with another one in a single-schema search path, or restore the default search path (use the Set default search path link).
To form a search path that includes two or more schemas, open the popup and use:
- Space to add a highlighted schema to the search path and also to remove a schema from the search path.
- Alt+Up and Alt+Down to reorder the schemas within the search path.
- Enter to apply the changes, i.e. to set the search path.
More instructions and usage hints are available right in the popup.
Composing SQL statements
When composing your SQL statements, use:
- Predefined patterns (Ctrl+J or Code | Insert Live Template).
- Auto-completion and highlighting of SQL keywords, and table and column names.
- Data type prompts for columns (Ctrl+P or ).
- Advanced find and replace capabilities (Ctrl+F or , and Ctrl+R or ).
- The console history ( or Ctrl+Alt+E). See Executing auto-memorized statements.
Editing data for INSERT statements in table format
- Select the
INSERTstatement of interest.
- Select Edit as Table from the context menu. As a result, the table editor opens.
- Use context menu commands and associated shortcuts for working with the data in the table editor.
Navigating to a table or column view in the Database tool window
When composing a statement, it's sometimes useful to take a look at the structure of a table, or to see the info about a column (field) in the context of the table to which it belongs. For such purposes, IntelliJ IDEA provides the ability to switch from the name of a table or column in the input pane to its view in the Database tool window.
The following ways are available for using this feature:
- Place the cursor within the name of the table or column of interest. Then use Ctrl+B. (Alternatively, you can use from the main menu or from the context menu.)
- Press and hold the Ctrl key, and point to the name of interest. When the text turns into a hyperlink, click the hyperlink.
Configuring the Execute command
IntelliJ IDEA provides many options for the Execute command depending on the cursor position and on whether there is a selection.
The options are specified on the page in the Settings / Preferences dialog ( on Windows and Linux; on macOS). For more information, see Execute in Console.
Executing an SQL statement
- Place the cursor within the statement.
- Do one of the following:
- Select the statement or statements to be run. (The suggestion list always contains an item for running all the statements.)
See also, Execute in Console.
Executing parameterized statements
Your statements can contain parameters, however, by the time you execute such statements the values of the parameters must be specified. There are the following ways of specifying the parameter values:
- Click on the toolbar or press Ctrl+Enter to execute the statement. In the dialog that opens, specify the parameter values and click OK.
( To start editing a value, switch to the corresponding table cell and start typing. To indicate that you have finished editing a value, press Enter or switch to a different cell. To quit the editing mode and restore an initial value, press Escape. )
- Alternatively, you can open the Parameters pane in the Database Console tool window ( on the toolbar) and specify the corresponding values there. (The values are edited in the same way as in the corresponding dialog.) Then execute the statement ( on the toolbar or Ctrl+Enter).
For more information, see Parameters pane.
Executing a group of statements
To execute a group of statements that follow one another in the console, select (highlight) the statements (to select all the statements, use Ctrl+A) and do one of the following:
Executing all statements
This command is available in the context menu, and its keyboard equivalent is Ctrl+Shift+F10.
Executing a part of a statement (e.g. a subquery)
To execute a part of a statement (e. g. a subquery), select (highlight) the fragment that you want to execute and do one of the following:
See also, Execute in Console.
Executing auto-memorized statements
As you run SQL statements in the consoles, IntelliJ IDEA memorizes them. So, at a later time, you can view the statements you have already run and, if necessary, run them again.
There are two panes in the History dialog. The left-hand pane shows the list of the statements that you have run. For "long" statements, only their beginnings are shown. When you select a statement in this pane, the overall statement is shown in the pane to the right.
You can filter the information: just start typing. As a result, only the statements that contain the typed text will be shown.
- Double-click the statement to be copied.
- Select the statement of interest and press Enter.
- Select the statement and click OK.
(Once the statement is in the input pane, you can run it straight away.)
Outputting the result of a SELECT statement into a file
- Right-click the
SELECTstatement of interest.
- Point to Execute to File and select the output format.
- Specify the output file location and name.
Using the error notification bar
If when running a statement an error occurs, an error notification bar appears in the lower part of the input pane.
This bar may be particularly useful when executing a sequence of statements (see Executing a group of statements) because in such a case it lets you select how to react.
The options are:
- Retry. Execute the sequence of statements starting from the one that caused the error.
- Ignore. Skip the erroneous statement and execute the sequence starting from the next statement. If another error occurs, the error notification bar will appear again.
- Ignore All. Skip the erroneous statement and execute the sequence starting from the next statement. If other errors occur, all the erroneous statements will be skipped and the error notification bar won't appear for these statements.
- Stop. Stop the execution of the sequence.
Canceling running statements
To terminate execution of the current statement or statements, do one of the following:
- Click on the toolbar of the input pane, or on the toolbar of the Dababase Console tool window.
- Press Ctrl+F2 .
Managing database transactions
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.
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.
Showing execution plans
The following context menu commands let you show an execution plan (a.k.a. explain plan) for a statement:
- Explain Plan. The result is shown in a mixed tree/table format on a dedicated Plan tab.
- Explain Plan (Raw). The result is shown in table format. (Technically,
EXPLAIN <CURRENT_STATEMENT>or similar statement is executed.)