Basic operations with tables
Create a table
In the Database tool window, select a data source and navigate to .
In the Create New Table dialog, specify table settings (columns, keys, indexes, foreign keys).
Delete a table
To delete a table, right-click the table and select DROP (N/A).
You can set additional options for the code that is generated in the SQL Preview field:
Qualify objects with schema names: adds a schema name to the table name. You can qualify a table when you have two and more tables with identical names in different schemes. This option has the following parameters:
Auto: automatically qualifies table names if you have more than two identical table names in different schemes.
Never: never qualifies table names.
Always: always qualifies table names.
Use IF EXISTS syntax: ensures that the table exists.
Use DROP CASCADE syntax: deletes objects that depend on the table (such as views).
Modify a table
In the Database tool window, right-click a table and select Modify Table.
In the Modify table dialog, specify the table settings that you need.
View table data
Open the Database tool window ( ).
In the database tree, navigate to the table that you want to open.
Double-click the table (F4).
Enable read-only mode for a table
To protect a table from accidental modifications in the data editor, you can make the table read-only. To enable read-only mode, click the Click to toggle the read-only attribute icon () in the lower-right corner of the editor.
To turn off read-only mode, click the Click to toggle the read-only attribute icon again.
Filter table data with a query
You can filter data in a table by writing a query in the Row Filter field.
If the Row Filter is not available, click the settings icon () and select Row Filter.
In the Row Filter field, type your query. The query syntax is the same as in the
WHEREclause but without the
WHEREkeyword. For example,
first_name LIKE 'Joh%' AND last_name LIKE 'lol%'.
You can use SQL wildcards within the
LIKEexpressions. For example, the percent sign
%for zero or more characters and underscore
_for a single character.
Press Enter. To reset the filter, click the clear icon (), or delete the contents of the Row Filter field and press Enter.
Use quick filtering options
You can specify filtering conditions manually or use quick filter options. Quick filter options are filtering conditions for the current column name. The conditions depend on the value in the current cell.
Right-click a cell and navigate to Filter by.
Select an option that you want to apply.
Filter rows when you run a search
You can filter rows when you run a search (Ctrl+F) on the table.
Press Ctrl+F and select Filter Rows.
Start typing your search query (for example,
Compare two table structures
You can compare two table structures and see the differences in columns, keys, indexes, and other structural table elements. To compare data in selected tables, see Compare table data.
Select two tables.
Right-click the selection and navigate to Compare.
Compare table data
You can compare data that is stored in two tables.
Double-click two tables that you want to compare.
In the editor, click the Compare with button () and select the second table.
If needed, change the 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
1in the Tolerance field.
Transpose a table
You can rotate the table data from rows to columns and from columns to rows. In the transposed view, the rows and columns are interchanged.
To transpose a table, click the settings icon () and select Transpose.
Export a table to a file
When you copy table data to the clipboard or save it in a file, the data is converted into one of the available output formats. As an output format, you can select SQL
UPDATE statements, TSV or CSV, an HTML table or JSON data.
Open a table or a result set, click the Dump Data icon () and select To File.
Export a table to a clipboard
On the toolbar, click the Dump Data icon () and select To Clipboard.
Configure data output format and options
You can configure how extracted data is converted into text using predefined output formats, customized formats, and scripts. To configure the output for extracted data, right-click any cell in the table and point to Data Extractor.
In this menu, you can select the output format (for example, as SQL
INSERT statements, HTML table, or in CSV format) or a script that converts data into a specific format. The following additional options are also available:
- Skip Computed Columns (SQL)
- Enable to exclude columns with calculated values (affects SQL output formats).
- Skip Generated Columns (SQL)
- Enable to exclude columns with auto-increment values (affects SQL output formats).
- Add Table Definition (SQL)
- Enable to include the
CREATE TABLEstatement (affects SQL output formats).
Additionally, you can configure the following parameters:
Configure CSV Formats. Opens the CSV Formats dialog that you can use to manage your delimiter-separated values formats (for example, CSV or TSV).
Go to Scripts Directory. Opens a directory with the scripts that convert table data into various output formats.
Export data to a database
Make sure that the database is added as a data source in the Database tool window.
Right-click any cell in the table and then click Export to Database.
Specify the database, target schema (to create a new table with the exported data) or table (to add exported data to an existing table).
Configure the data mapping and settings for the target table.
Copy and paste cells between tables
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.
Copy a table to another schema
You can create a copy of a table in the schema of the current data source or select another data source and create a copy here.
Right-click a table and select Copy Table to. Alternatively, press F5.
Enter a schema name and click OK.
(Optional) In the Import dialog, modify table settings.
Update the table view
You need to update the table view if you want to synchronize the data that you see in the editor with the contents of the database. Or, when you want to apply the page size limit setting after its change.
To refresh the table view, consider the following actions:
Click the Synchronize icon () on the toolbar.
Right-click the table and select Reload Page from the context menu.
Reset the initial table view
Click the settings icon () and select Reset View. As a result, the data becomes unsorted, the columns appear in the order they were defined initially, and all the columns are shown.
View the query that generated the table
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, press Escape.