CLion 2018.3 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, indices, foreign keys).

  3. Click OK.

Delete a table

To delete a table, 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).

Open a table for editing

  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.

Enable the 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 the 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 the 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 similar to a WHERE clause syntax but without the word WHERE. 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. PressEnter. To reset the filter, click the clear icon (The clear icon), or delete the contents of the Row Filter field and press Enter.

Using quick filtering options

You can specify filtering conditions manually or use quick filter options. Quick filter options are a set of 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.

Compare tables

You can compare the current table with any other table that is open in a data editor or shown in the Database Console tool window.

  1. Select a table.

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

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

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 a 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 table 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 scheme

You can create a copy of a table in the scheme 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.

  • PressCtrl+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.

Restore the initial table view

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, pressEscape.

Last modified: 15 January 2019