CLion 2019.2 Help

Basic operations with tables

Create a table

  1. In the Database tool window, select a data source and navigate to File | New | Table.

  2. In the Create New Table dialog, specify table settings (columns, keys, indexes, foreign keys).

  3. Click OK.

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

  1. In the Database tool window, right-click a table and select Modify Table.

  2. In the Modify table dialog, specify the table settings that you need.

View table data

  1. Open the Database tool window (View | Tool Windows | Database).

  2. In the database tree, navigate to the table that you want to open.

  3. 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 (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.

Enable the read-only mode for a table

Filter table data with a query

You can filter data in a table by writing a query in the Row Filter field.

  1. If the Row Filter is not available, click the settings icon (the settings icon ) and select Row Filter.

  2. In the Row Filter field, type your query. The query syntax is the same as in the WHERE clause but without the WHERE keyword. For example, first_name LIKE 'Joh%' AND last_name LIKE 'lol%'.

    You can use SQL wildcards within the LIKE expressions. For example, the percent sign % for zero or more characters and underscore _ for a single character.

  3. Press Enter. To reset the filter, click the clear icon (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.

  1. Right-click a cell and navigate to Filter by.

  2. Select an option that you want to apply.

You can filter rows when you run a search Ctrl+F on the table.

  1. Press Ctrl+F and select Filter Rows.

  2. Start typing your search query (for example, John).

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.

  1. In the Database tool window (View | Tool Windows | Database), select two tables.

  2. Right-click the selection and navigate to Compare Ctrl+D.

Compare two tables

Compare table data

You can compare data that is stored in two tables.

  1. Double-click two tables that you want to compare.

  2. In the editor, click the Compare with button (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 1 in the Tolerance field.

Compare two tables

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 (The table 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 INSERT or UPDATE statements, TSV or CSV, an HTML table or JSON data.

Open a table or a result set, click the Dump Data icon (The Dump Data icon) and select To File.

Export a view to a file

Export a table to a clipboard

On the toolbar, click the Dump Data icon (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.

The data editor context menu

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 TABLE statement (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

  1. Make sure that the database is added as a data source in the Database tool window.

  2. Right-click any cell in the table and then click Export to Database.

  3. Specify the database, target schema (to create a new table with the exported data) or table (to add exported data to an existing table).

  4. 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, CLion 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.

  1. Right-click a table and select Copy Table to. Alternatively, press F5.

  2. Enter a schema name and click OK.

  3. (Optional) In the Import dialog, modify table settings.

  4. Press OK.

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 The Synchronize icon on the toolbar.

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

  • Press Ctrl+F5.

Reset the initial table view

Click the settings icon (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.

Last modified: 3 October 2019