Note: This feature is available in Community, Enterprise, Ultimate and Team editions only.

Overview

Data Migration in DBeaver provides the functionality to transfer data between different databases or between tables within the same database. This guide walks you through the steps to execute a data migration using DBeaver.

Data migration process

Step 1 Define the data source

To initiate the data migration, you need to select your data source. Follow the steps below:

1. Navigate to the Database Navigator.
2. Select one or multiple tables that you want to export.
3. Right-click to open the context menu.
4. Choose Export Data from the options.

Tip: You can export data from a custom SQL query. Execute the query and then choose Export Data from the results context menu.

Step 2 Define data transfer target type

After selecting the data source, the next step is to specify the type of destination for the data transfer. Choose Database type as the data transfer target and press Next.

Step 3 Tables mapping

Once you have chosen Database as the transfer target type, the next step involves mapping data. This process includes specifying options, selecting the target container, and setting other configurations. Configure your data mapping settings and press Next to proceed to the next step.

Available options

Buttons:

Icon	Option	Description
	Browse	Allows selection of target container.
	Configure	Opens additional settings. For details, see the Configure section.
	Preview data	Shows a preview of the data to be transferred.
	Up	Moves the selected mapping closer to the head of the queue.
	Down	Moves the selected mapping closer to the tail of the queue.
	Mapping Rules	Choose how new tables and column names are transformed when transferring data. For details, see Mapping Rules section.
	Auto assign	Automatically populates the Target column based on the source.
	Choose…	Specify the target container where the data will be transferred in.

Fields:

Option	Description
Target container	Defines the database or schema for the data transfer. For more details, see the Target Container section.
Source	Displays the names of selected tables and their columns.
Target	Shows the names of destination tables.
Mapping	Lists the available actions for data transfer. For more details, see the Mapping section.
Transform	Allows for the transformation of column values during the data transfer. For more details, see the Transform section.

Target container and target table

Specify target container

Press the Choose… button to select the container where the data will be transferred.

Define a target table

You can specify the target table where the data will be transferred in multiple ways:

• Manual Entry: Click on a cell in the Target column and manually enter the name of the table where you wish to transfer the data.

• Drop-down list: Use the drop-down list next to the Target column to choose among the following options:

  • Pre-existing table names.
  • Skip: Skips the data transfer for this table.
  • Browse: Opens the Choose Target Table window

• Browse button: Alternatively, press the Browse button to select a table from the existing tables in the target container.

  

Mapping

To change the mapping type, click a cell in the Mapping column of Table mapping dialog box and select the required mapping type.

Action	Description
Create	Transfers source data into a newly created table or column in the target container.
Skip	Does not transfer the source data.
Existing	Transfers source data to an existing table in the target container.
Recreate	Recreate the table, which means that the available data, keys, indexes, and other possible entities of the existing table will be lost.

Tip: If the cells are marked with , it means that in the target table, there are no columns with matching names, otherwise the names will be filled in automatically.

Transform column values

You may also want to transform the values of some columns during the transfer. To do that, define column transformers by clicking on corresponding cells in the Transform column. You can choose one of three options:

Option	Description
Set to NULL	All values in the corresponding column are set to null .
Constant	Sets column value to a constant value.
Expression	Uses JEXL expressions to calculate the column’s value.

Configure

Click the Configure button to open the Configure metadata structure window. This window allows you to delve into additional settings are distributed across the following tabs:

Column mapping

By navigating to the Column mapping, you can explore detailed mapping between the source and target columns.

Element	Description
Source Column	Contains names of columns existing in the selected source table.
Source Type	Lists the data types assigned to the columns in the selected source table.
Target Column	Contains names of columns in the target table where the data from the source column will be transferred.
Target Type	Lists the data types that will be assigned to the columns in the target table.
Mapping	Contains the list of actions to be applied to the data on data transfer.
Transform	Displays transformations for the data in a column during the transfer.

Important: Data types that are supported in the source database may not be supported in the target, and vice versa. To set a data type for a target column, click the cell in the Target Type column and choose from the dropdown list.

Table properties

By selecting the Table properties tab, you can modify properties of the target table, such as:

Property	Description
Tablespace	Specifies the tablespace for the target table.
Partition By	Sets the partitioning for the target table.
Comment	Allows you to add comments to the target table.

Note: The availability of these settings may vary depending on the database you are using.

Target DDL

By selecting the Target DDL tab, you can view the SQL script that will be executed during the data transfer.

If you’re exporting data to a new table or recreating an existing one, the tab will display the necessary SQL statements. If not, the DDL tab will remain empty.

Mapping Rules

Clicking the Mapping Rules button opens a window that provides options for customizing how new tables and column names are transformed during the data transfer.

In the window, the following settings are available:

Option	Description
Name case	Sets the letter casing for table and column names. Choices include Default, Upper case, and Lower case.
Replace spaces	Determines how spaces in table and column names are handled. Options are: Do not replace, Replace with underscore, and Remove, convert to CamelCase.
Max data type length	Sets the maximum length for data types.
Save current changes to global settings	If checked, saves the current mapping rules to global settings. Otherwise, they will be saved at the data source settings level.
Global settings	Opens the global settings window for Names Mapping Rules. Alternatively, to open global settings, go to Window -> Preferences -> Connections -> Data Transfer

In addition to using the Global settings button, you can also access these settings by navigating to Window -> Preferences -> Connections -> Data Transfer.

Important: After modifying the Mapping Rules settings, you will be prompted to confirm your changes. Modifying the Mapping Rules may result in the loss of names that were already changed.

Keyboard Shortcuts

The following keyboard shortcuts for easy navigation within the mapping table area of the Tables mapping tab are supported:

Shortcut	Action
Up	Move one row up.
Down	Move one row down.
Right	Expand list of source table columns.
Left	Collapse list of source table columns.
Space	Auto-assigns the target.
Del	Sets mapping type to skip.

Step 4 Extraction settings

After setting up your table mappings, the next step is to define how the data will be extracted from the source. The Extraction settings tab offers various options to optimize this process. Configure these settings to suit your specific data transfer requirements and press Next.

Option	Description
Maximum threads	Defines the number of threads to be used for data transfer.
Extract type	Use Multiple queries if database doesn’t support resultset scrolling. You can also set the Segment Size value when this option is selected.
Open new connection(s)	If checked, a new connection is established, ensuring data transfer does not affect other database operations.
Select row count	Enables a progress bar to display data migration status.
Fetch size	Indicates the number of rows fetched per server round trip, affecting extraction performance.

Step 5 Data load settings

After configuring the extraction settings, you’ll need to specify how the data will be loaded into the target database. The Data load settings tab provides a range of options to control this part of the process. Adjust the settings according to your needs and then press Next.

Data load settings tab defines how the extracted data will be pushed to the target. The following options are available.

Option	Description
Transfer auto-generated columns	Fill in or skip columns marked with the “autogenerated” status. Some databases accept values in such columns, while others will throw a syntax error.
Truncate target table(s) before load	Select this checkbox only if you want all the data to be cleared from the target table. Be very careful with this option!
Disable referential integrity checks during the transfer	Disabling constraints in the target table. This setting prevents database errors by temporarily disabling the constraints. However, please note that not all databases support this functionality.
Replace method	Read our guide on Data Import and Replace to learn more about the replacing method option.
Open new connection(s)	Use this option to speed up data transfer. If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database where data is being transferred to.
Use transactions	This option allows you to speed up the data transfer and to define the number of rows for each transaction by setting the Commit after insert of parameter.
Do Commit after row insert	Performing a commit after a certain number of inserted rows. This setting specifies that a commit operation should be executed after a specified number of rows have been inserted into the table.
Use multi-row value insert	Use multi-row insert with extended values number for higher performance. Database-specific setting.
Skip bind values during insert	This option can drastically increase performance for some drivers like Redshift by skipping a process of binding values and setting them directly, but it opens up a vulnerability to SQL injections. Not recommended if you are not sure of imported file contents.
Disable batches	Select this checkbox if you want to disable the use of batch imports. The import will be made row by row. Enabling this function will show all import errors, but make the import process slower.
Ignore duplicate rows errors	In the import process, if a database encounters a duplicate key from the import row in the target table, such errors are ignored, and the import operation continues without failure.
Use bulk load	Bypasses transaction settings and loads the entire dataset using the native tool provided by the database.
Open table editor on finish	If selected, the table editor is to be opened when data transfer is finished.
Show finish message	If selected, a notification message will be shown when the transfer is finished.
Send results by E-Mail	Sends data transfer results by E-Mail on finish. Read our guide to using email to transfer data to learn more.

Step 6 Confirm

The final step before initiating the data transfer is the Confirm tab. This tab provides a summary of all configurations set up in the previous steps. It serves as a last review to ensure all settings are correct.

Here is a table summarizing the configurations:

Option	Description
Source Container	Displays the source container from which data will be pulled.
Source	Shows the specific source within the container.
Target Container	Displays the target container to which data will be pushed.
Target	Shows the specific target within the container.
Source Settings	Summarizes settings related to data extraction from the source.
Target Settings	Summarizes settings related to data loading into the target.

These settings are final and cannot be changed at this stage. If all configurations are correct, click Finish to start the data transfer.

Tip: You can save these configurations for future use and create a task related to the data transfer operation by selecting Save task. This option consolidates your settings into a reusable task.

Upon clicking Proceed, the data transfer process will initiate.

Step 7 Export completion notification

If there are no errors, you will see a notification window indicating the successful completion of the export task. You can continue working with your database during the export process, as it will be performed in the background.

Note: This feature is available in Lite, Enterprise, Ultimate and Team editions only.

DBeaver offers a way to send data exported via Data Transfer by email.

SMTP profile configuration

First, you’ll need to add an SMTP profile to send the email. Go to Window ⇒ Preferences ⇒ General ⇒ Mail and create a new profile.

Parameters Host and Port may depend on the mail service you use. Use the latter if the service offers SSL and TLS ports. Gmail, for example, uses host

and port . An example of a configured profile:

Then you can use the Test connection button to verify that the host and credentials are valid.

Please take a look at the troubleshooting section for more information on resolving common authorization problems.

Setting up data transfer

After configuring at least one SMTP profile, you are able to set up the email notification for data transfer. To do this, follow these steps:

1. Create an export task.
2. Navigate to the Output page.
3. Ensure the Send results by E-mail option is checked.
4. Click the Configure button to set up email details.

Within the email configuration, define the following settings:

Setting	Description
Send success reports	Sends an email upon successful completion of a task.
Don’t attach produced files	Excludes the output files from the email notification.
Don’t send if produced files are empty	Prevents sending an email when the output files have no data.
Send failure reports	Notifies by email if a task encounters a failure.
Bypass server certificate verification	Disables server certificate validation, useful for self-signed certificates.

Once the task runs, the recipients you configure will receive an email. The email will contain the exported file in the selected format if the appropriate options are enabled.

Authorization troubleshooting

You may face various problems while setting up a new SMTP profile.

Several common errors when using Gmail and solutions for them are described below:

• 535-5.7.8 Username and Password not accepted
  . Check that the username and password are correct. If you are confident that you have entered valid credentials, then try creating an app password.
  Read more at https://support.google.com/accounts/answer/185833
• 534-5.7.9 Application-specific password required
  . You have two-factor authorization enabled. You will need to generate a special password for DBeaver to use.
  Read more at https://support.google.com/accounts/answer/185833

There were also several cases when the antivirus would block DBeaver from sending an email.

Note: This feature is available in Enterprise, Ultimate and Team editions only.

Using Data Transfer, you can export files to external storage, such as Google Drive.

Exporting files to Google Drive

1. Download and install Google Drive for desktop here
2. Configure Drive for streaming mode according to this article

After that, in the data transfer wizard, change the output directory to the Drive directory.

Exporting automatically

It can be achieved by using Task Scheduler.

Overview

Data transfer is a crucial feature that enables you to export and import data in various formats as well as moving data between tables in the same or to different databases.

Note: The data transfer operation runs in the background, allowing you to continue working with your database during the export or import process. However, try to avoid changing data in the tables that have been selected for export or import until the process is complete.

Supported formats

Export	Import
Database	CSV
CSV	XLSX
DbUnit	XML
HTML	Table
JSON
Markdown
SQL
Source code
TXT
XML
Parquet
XLSX

Transferring data between database tables

DBeaver provides data migration capabilities, allowing you to both export and import data between database tables. You can transfer data from one table to another within the same database or between different databases.

For more detailed instructions, see Data Migration.

Export data

The process of exporting data follows a similar procedure for the supported formats. Therefore, in this guide, we will focus on the data exporting process using the

format.

Note: Each format may have specific configuration settings that can be adjusted to meet your needs.

1. Select the table or tables you want to export. In the context menu, choose Export Data.

   

   Note: you can also export data from custom SQL query results. To do that, choose Export data in the results context menu.

2. In the window that appears, choose

   CSV
   and click Next.

   

3. Set your data extraction options (how the data will be read from the tables). This may affect the extraction’s performance. And set the export format option. They are specific to the data format you chose in step 2:

   

   Setting name	Description
   Maximum threads	Number of threads used for data extraction.
   Extract type	Defines the method of data extraction ( Single query or Multiple queries ).
   Segment size	The size of data segments to be extracted at a time (active while Multiple queries are selected as an Extract type).
   Open new connection(s)	Opens a new physical connection for data reading, recommended if you plan to continue working with the database during the export process.
   Select row count	Queries row count before performing export to track progress, but may cause performance issues in some cases.
   Fetch size	Number of rows the driver retrieves in one batch from the database.

   Tip: The Maximum threads option is active when exporting data from multiple tables. It is recommended to set the number of threads to match the number of cores in your computer for optimal performance.

4. Configure the format settings.

   At this stage, you configure the format settings for the output file. These settings determine how the data will be presented and encoded in the exported file. For more details on exporter settings, see Export parameters.

   

   Setting name	Description
   Formatting	Selects the formatting settings for the exported data. Reed more in the related article
   Binaries	Specifies how binary data is represented in the export.
   Encoding	Sets the character encoding for the exported file.
   Value display format	Chooses the format for displaying values in the export.
   Configure columns	Allows you to set which columns to export. Configure the columns to export by clicking Configure. In the Action column, use the down arrow on a row to toggle between skip or export for that column/table.

   Note: The options within the Exporter settings section will vary and provide specific controls tailored to the format selected for export.

5. Set options for output files or clipboards.

   

6. Review what you want to format and which format you will export. You can also save all your settings as a task in this step or change the task variables:

   

7. Press finish. See extraction progress. You can keep working with your database during the export process as the extraction will be performed in the background.

   

Import data

The process of importing data follows a similar procedure for the supported formats. Therefore, in this guide, we will focus on the data exporting process using the

format.

1. Choose the database table (or tables) you want to import data into. Do this by right-clicking on the table name in the Database Navigator section and then clicking on Import Data.

   

2. In the window that appears, choose

   XLSX
   and click Next.

   

3. In the following window, choose the file that contains the data you wish to import into the table, select the appropriate settings, then click Next. For more details on importer settings, see Import parameters.

   

4. In the next window, set XLSX-to-table mappings. Please refer to our mapping process guide for more detailed information.

   

5. Select your data load settings in the subsequent window, and then click Next. For more information, please refer to our article’s section Data load settings article.

   

6. In the final window, you can review all the settings you selected earlier. If you missed something, you could go Back and fix it. When you are ready, finish the import by clicking Proceed.

   

7. If the

   XLSX
   file is valid and there are no errors, you will see a notification window with information about the completion of the task. You can keep working with your database during export, as the data loading will be performed in the background.

   

Export parameters

In this section, you will find specific configuration settings for each supported export format. These settings allow you to customize the export process according to the requirements of the data format you choose.

CSV

Setting name	Description	Available options
Characters escape	Bad characters escaping model (surrounded with quotes or escaped with ‘\’ character).	quotes / escape
Delimiter	Column delimiter.	You can use special characters like \t , \n , and \r .
File extension	The default file extension for the exported file.	csv
Format numbers	Format numeric values using locale settings.	true / false
Header	CSV header settings.	none / top / bottom
Header case	You can choose lower or upper case for column names or descriptions in the header.	as is / upper / lower
Header format	Defines the formatting of the header.	label / description / both
NULL string	String which will be used instead of NULL values.
Quote always	Quote all cell values. Cannot be used with “Quote Never”.	disabled / all / strings / all but numbers / all but nulls
Quote character	Character which will be used to quote strings (space means no quote).
Quote never	Do not quote cell values. Cannot be used with “Quote Always”.	true / false
Row delimiter	Row delimiter. Default is system-specific line feed delimiter.	default / \n / \r / \r\n / \n\r

DBUnit

Setting name	Description	Available options
File extension	The default file extension for the exported file.	xml
Force upper case column names	Convert all column names to upper case.	true / false
Force upper case table name	Convert the table name to upper case.	true / false
Include NULL values in export	Include NULL values in the exported data.	true / false
Replace NULL values with	Specify a string to replace NULL values in the export.

JSON

Setting name	Description	Available options
File extension	The default file extension for the exported file.	json
Format dates in ISO 8601	Convert all date values to ISO 8601 format.	true / false
Print table name	Include the table name in the exported file.	true / false
Export JSON values as	Export values as text or as JSON objects.	string / JSON

Markdown

Setting name	Description	Available Options
Confluence format	Enable Confluence-specific Markdown formatting.	true / false
File extension	The default file extension for the exported file.	md
Format numbers	Format numeric values using locale settings.	true / false
NULL string	String to represent NULL values in the export.
Show header separator	Include a separator line below the header.	true / false

Source code

Setting name	Description	Available options
File extension	The default file extension for the exported file.	php
Format dates in ISO 8601	Convert all date values to ISO 8601 format.	true / false
Language	Specifies the PHP version compatibility for the generated code.	PHP < 5.4 , PHP 5.4+
Quote character	Character which will be used to quote strings.	" , '
Row delimiter	Row delimiter. Default is system-specific line feed delimiter.	default , \n , \r , \r\n , \n\r

SQL

Setting name	Description	Available options
Data rows per statement	Specifies the number of data rows in a single insert statement.	integer
File extension	The default file extension for the exported file.	sql
Identifier case	Allows selection of lower or upper keyword case for table and column names.	as is / upper / lower
Include generated columns	Specifies whether to include auto-generated columns (e.g., auto-increment) in SQL INSERT statements.	true / false
Insert line before rows	Specifies inserting a line feed before values in multi-row inserts.	true / false
Keyword case	Allows selection of lower or upper keyword case.	upper / lower
Native date/time format	Specifies using native date/time format in INSERT statements.	true / false
Omit schema name	Specifies omitting schema/catalog name in INSERT statements.	true / false
On conflict expression	Provides an expression for the end of the statement. This setting is specific to the database.
Target table name	Allows specification of the target table name to generate an INSERT statement.
Upsert keyword	Allows selection of different upsert keywords.	INSERT / INSERT ALL / UPDATE OR / UPSERT INTO / REPLACE INTO / ON DUPLICATE KEY UPDATE / ON CONFLICT

TXT

Setting name	Description	Available options
Batch size	Specifies the number of records per batch.	integer
File extension	Specifies the file type for output.	txt
In-between delimiter	Adds a custom character between data values.
Max column length	Specifies the maximum length of data in a column; longer values will be cropped.	integer
Min column length	Specifies the minimum length of data in a column; shorter values will be padded with spaces.	integer
Print header	Specifies whether to print column names at the top of the file.	true / false
Show header delimiter	Adds hyphen characters either in the first row without a header or between the header and data.	true / false
Show leading delimiter	Adds a pipe character at the start of the row.	true / false
Show NULLs	Controls the display of NULL values in the output.	true / false
Show trailing delimiter	Adds a pipe character at the end of the row.	true / false

XML

Setting name	Description	Available options
File extension	Specifies the file type for output.	xml
Include DOCTYPE declaration	Specifies whether to include the DOCTYPE declaration in the XML file export.	true / false

XLSX

Setting name	Description	Available options
Append strategy	Strategy used when appending data to an existing file.	create new sheets / use existing sheets
Boolean string FALSE	String that replaces FALSE boolean values in the exported file.	true / false
Boolean string TRUE	String that replaces TRUE boolean values in the exported file.	true / false
Border style	Style of cell borders in the exported file.	NONE / THIN / THICK
Excel date format	Date and time format used in the Excel file (modifiable in Excel application).	m/d/yy / d-mmm-yy / d-mmm / mmm-yy / h:mm AM/PM / h:mm:ss AM/PM / h:mm / h:mm:ss / m/d/yy h:mm
Column group	Column number used for grouping rows on a sheet by column value.	integer
Export SQL	Specifies whether to export SQL to a second sheet in the Excel file.	true / false
File extension	File extension for the exported document.	xlsx
Header format	Format of the header in the exported file.	label / description / both / none
Header row font	Font styling for the first row in the exported file.	NONE / BOLD / ITALIC / STRIKEOUT / UNDERLINE
Max row on sheet	Maximum number of rows allowed on a single sheet, after which data will split into another sheet.	integer
NULL string	String used instead of displaying NULL values in the exported file.
Row number(s)	Includes the row index as the first column in the exported file.	true / false
Split SQL Text	Splits exported SQL into rows by CR (Carriage Return) in the exported file.	true / false
Trim strings	Removes extra leading and trailing spaces from all string values in the exported file.	true / false

HTML

Setting name	Description	Available options
File extension	File extension for the exported document.	html
Images	Extracts images to graphic files.	true / false
Output column headers	Outputs column names as an additional row in the generated table.	true / false
Output table header	Outputs the query or table name as the first row in the generated table.	true / false

Import parameters

Setting name	Description
Header position	Determines the location of the column names in the Excel table, either at the top or none. This setting specifies whether the column names are located in the first row of the Excel table or if there are no column names present.
Skip empty rows	If this setting is enabled, any open string values encountered during the data processing will be ignored and not inserted into the corresponding cells in the row. If the setting is disabled, all cells in the row will be filled with a NULL value if an empty string is encountered.
Import all sheets	Specifies that all sheets in the file should be imported during the data import process.
Specific sheet name	Enables you to choose a particular sheet from the Excel file for importing during the data transfer process.
Date/time format	Use this setting to specify the date format used in the XLSX file. This is used to clarify the date format during the import process and does not affect the output data. You can refer to the section Patterns for data import for details on the format pattern syntax.
Timezone ID	The local machine timezone is used by default. There are three ways to specify the timezone: 1) Local zone offset: Specify the offset from UTC in the format of either a positive or negative number (e.g., +3, -04:30). 2) Specific zone offset: Specify the offset from GMT or UTC in the format of GMT+/-X or UTC+/-X (e.g., GMT+2, UTC+01:00). 3) Region-based: Specify the timezone using a region-based identifier such as UTC, ECT, PST, etc.
Sample rows count	Determines the number of rows that will be used as a sample to estimate the length and data types of the imported data.
Minimum column length	This value is used when creating a new column and, if necessary, specifying its type. It indicates the minimum number of characters or digits expected in the column. This information helps determine the appropriate data type and size for the column during the creation process.
Save task	Opens the Save Task window to assist in creating a task during the data transfer process. This window provides options and settings for creating and configuring a task related to the data transfer operation.

Patterns for data and time format

When specifying the date/time format for data import, ensure that you use the correct format specifiers. A common issue involves confusing format codes, such as using

(for months) when (for minutes) is intended.

Refer to the table below for a list of common format symbols and their meanings.

Symbol	Meaning	Presentation	Examples
G	era	text	AD; Anno Domini; A
u	year	year	2004; 04
y	year-of-era	year	2004; 04
D	day-of-year	number	189
M/L	month-of-year	number/text	7; 07; Jul; July; J
d	day-of-month	number	10
Q/q	quarter-of-year	number/text	3; 03; Q3; 3rd quarter
Y	week-based-year	year	1996; 96
w	week-of-week-based-year	number	27
W	week-of-month	number	4
E	day-of-week	text	Tue; Tuesday; T
e/c	localized day-of-week	number/text	2; 02; Tue; Tuesday; T
F	week-of-month	number	3
a	am-pm-of-day	text	PM
h	clock-hour-of-am-pm (1-12)	number	12
K	hour-of-am-pm (0-11)	number	0
k	clock-hour-of-am-pm (1-24)	number	0
H	hour-of-day (0-23)	number	0
m	minute-of-hour	number	30
s	second-of-minute	number	55
S	fraction-of-second	fraction	978
A	milli-of-day	number	1234
n	nano-of-second	number	987654321
N	nano-of-day	number	1234000000
V	time-zone ID	zone-id	America/Los_Angeles; Z; -08:30
z	time-zone name	zone-name	Pacific Standard Time; PST
O	localized zone-offset	offset-O	GMT+8; GMT+08:00; UTC-08:00;
X	zone-offset ‘Z’ for zero	offset-X	Z; -08; -0830; -08:30; -083015; -08:30:15;
x	zone-offset	offset-x	+0000; -08; -0830; -08:30; -083015; -08:30:15;
Z	zone-offset	offset-Z	+0000; -0800; -08:00;

For more information on valid patterns, see the Java DateTimeFormatter Patterns.

Overview

The Data editor appears:

• as the Data tab of the Database Object Editor, which is only available for tables and views;
• as the Results tab when you run a custom SQL query in SQL Editor.

The Data editor allows to view and edit data of a database table or view. The central part of the Data editor is the data grid. The editor also provides top toolbar, bottom toolbar, left side bar, right side bar and a filter field:

To learn how many rows the table data contains, click the Calculate total row count button in the bottom toolbar. The number of rows appears in a status field next to the button:

To learn about ways to navigate data in the table data, see Navigation article.

Top toolbar

The top toolbar contains the following buttons:

Button	Name	Description
	Remove all filters/orderings	Removes all filters and orderings applied to the data in the filter field.
	Save filter settings for current object	Saves the current filter settings for the database object to apply next time when you reopen it in the editor, see details in the Data Filters article.
	Custom Filters	Opens the Result Set Order/Filter Settings window. See Data Filters article for more information.
	Forward and backward – history navigation buttons	Navigate forward and backward in the Data Editor history, see the history section of our article for more information.

Left side bar

The left side bar contains the following tabs:

Button	Name	Description
	Grid	Switches to the grid view of data.
	Text	Switches to the plain text view of data.
	Spatial	Switches to the spatial view. For more details, see the GIS data article.
	Chart	Switches to the chart view. For more details on charts, see the Managing Charts article.
	Record	– Same as pressing Tab – Switches the positions of rows and columns so that the columns appear as rows, and the rows hide in one Value column. See details in the Table vs. Record Views section of our article.

Right side bar

The right side bar contains the following tabs (see the Panels for more information):

Button	Name	Description
	Panels	Opens panels on the right side of the Data Editor.
	Calc	Opens the result cells calculation panel ( SUM , MAX , AVG , etc.).
	Grouping	Opens grouping panel window tools.
	Metadata	Opens Metadata panel.
	References	Opens References panel.
	Value	Opens Value Viewer.
	Result Details/Query Trace	Opens the Result Details or Query Trace panel depending on the database.

Note: The Result Details panel is compatible with specific databases such as BigQuery, while the Query Trace panel is available for Cassandra and Yugabyte CQL. For guidance on enabling Query Trace, see Panels article.

Bottom toolbar

The bottom toolbar provides the following buttons:

Button	Name	Description
	Refresh	Refreshes the whole results set, including all items that are not visible on the screen, while its dropdown option allows to customize the refresh frequency over a specific period.
	Save	Saves all unsaved changes to the data such as adding, duplicating, deleting rows, inline editing of values. See the Data Viewing and Editing article for information.
	Cancel	Discards all unsaved changes to the data.
	Edit cell value in separate dialog/editor	Opens the cell in focus for editing in a separate editor or dialog box. See details in the Cell Editor section of our article.
	Add new row	Adds a new empty row below the current row, see details in the Adding, Copying and Deleting Rows section of our article.
	Duplicate current row	Copies the current rows and pastes the copy below the current row, see details in Adding, Copying and Deleting Rows section of our article.
	Delete current row	Colors the rows in focus in red to mark them for deletion, see details in the Adding, Copying and Deleting Rows section of our article.
	Move to first row	Moves the focus (highlighting) from the current to the first row of the table.
	Move to previous row	Moves the focus (highlighting) from the current to the previous row of the table.
	Move to next row	Moves the focus (highlighting) from the current to the next row of the table.
	Move to last row	Moves the focus (highlighting) from the current to the last row of the table.
	Fetch all data	Fetches the whole result set making it ready for display. See the Scrolling Results Page section of our article.
	Configure	Opens a menu with settings.
	Result-set fetch size	Displays the initial fetch number of rows in the result set on loading or refreshing.
	Calculate total row count	Calculates the total number of rows in the table.

Note: Some of these buttons may be disabled and may not work if you are using a read-only connection, connecting to a read-only database or if you see the result of a complex query, such as joining two or more tables.

Table status indicators

When working in the Data Editor grid, you might notice icons in the top left corner of each table. These icons are tools to help you quickly understand why you may encounter limitations while attempting to edit data in specific tables. Here is a guide to understanding what each icon means and how it can help you:

Icon	Indicator	Description
	No unique key was found. Data modification is not possible.	The table lacks a unique key, restricting editing.
	PRIMARY KEY column_name	A unique key enables editing of the table.
	VIRTUAL PRIMARY KEY column_name .	A virtual key enables editing of the table. For more information about virtual keys, see Virtual keys.
	Table metadata not found. Data edit is not possible.	Unavailable metadata prevents editing of the table.
	Read-only connection.	A read-only connection prevents any edits.

Column context menu

Each column has a context menu, accessed by clicking the downward arrow button , providing different filter options.

Cell context menu

Every cell in the table data also has a context menu – right-click the cell to open the menu. The context menu provides the following items:

Menu Item	Description
Copy	Copies the content of the current cell or column to the clipboard.
Advanced Copy	Opens advanced copy submenu that allows copying data with preset formatting parameters.
Paste	Pastes the copied content to the cells in focus.
Advanced Paste	Pastes several values delimited with a tabulation or line break starting from the selected cell.
Delete	Deletes the row that has the cell in focus. NOTE: In fact, when users click Delete, the system only highlights the red row while the actual deletion happens when users click Save.
Edit	Opens a submenu enabling inline editing. See the Data Viewing and Editing article.
Order	Displays a submenu that allows to specify ordering criteria for the data. The submenu contains the most common ordering options that can be applied to the column in focus. See details in Data Filters article. By default, DBeaver orders data by sending a request to the server (the Server-side results ordering checkbox selected). To order data on the client side using DBeaver’s internal algorithm, clear the checkbox.
Filter	Displays a submenu that allows to specify filter criteria for the data. The submenu contains the most common filters that can be applied to the cell in focus. See details in Data Filters article. By default, DBeaver filters data by sending a request to the server (the Server-side results ordering checkbox selected). To filter data on the client side using DBeaver’s internal algorithm, clear the checkbox.
View/Format	Opens a submenu that provides options for formatting and modifying the view of data. See the Data View and Format article.
Navigate	Opens a submenu that helps users to navigate through the table data. See the Navigation article.
Layout	Changes the layout of data, see the Data View and Format article.
Export data	Opens the Data Transfer wizard that guides you through the steps to export data to a selected format. See the Data Transfer article. Note: The system exports the whole result set including records that are not visible on the screen and preserves all applied data filters and ordering.
Generate SQL	Opens a submenu where you can select the type of SQL query to generate. See the SQL Generation article.
Generate Mock Data	Opens Mock Data Generator. See the Mock Data Generation in DBeaver article.
Logical structure	Opens a submenu allowing you to write virtual column expressions. See the article about Virtual column expressions.
Open with	Opens the data in external applications like Excel or a web browser.
Toggle result panel	Opens Value Viewer panel on the right side of the Data Editor.
Refresh	Refreshes the whole results set including all items that are not visible on the screen.

Additional features

Disabling metadata queries

If you want to improve performance and save money in databases where you pay for each query, you can stop reading the metadata queries in the Data Editor. This means the editor would not automatically ask for information about tables and columns.

To enable, navigate to Window -> Properties -> Connections -> Metadata and check the option Do not read tables information in SQL and data editors.

• Benefits:

  • Makes the Editor work faster because it runs fewer queries.
  • Saves money in databases where each query costs you, by only running queries that you start yourself.

• Disadvantages:

  • You won’t have auto-completion or other tools that need metadata.
  • Results from queries are read-only. To check if modifications are possible, look for an indicator in the top left corner of the table grid. For more information on indicators, see Table status indicators.

Tip: This setting is particularly valuable for managing databases like Google BigQuery or Snowflake.

You can apply custom filters to table contents or query results. There are several ways in which you can filter data in the table.

One of the ways is to use the filter field above the table next to the top toolbar. To filter data, enter an SQL expression into the field and click the Apply filter criteria button () next to the field or press Enter.

You can apply ready-to-use SQL expressions or SQL expression templates via the context menu. To select a ready SQL expression or a template, press F11 or right-click the cell, then click Order/Filter on the context menu and then click one of the expressions.

The third way is to filter data by a cell value using the filter icon in the column header. To filter data this way, click the filter icon in the column header and then double-click the cell value in the Filter by the column value dialog box:

The data updates dynamically. To remove a filter, click the Remove All Filters/Orderings button () in the top toolbar of the editor.

You can save the current filter settings for the database object to apply the next time you reopen it in the editor. To save the current filter settings, click the Save filter settings for current object button () in the top toolbar.

Advanced filters configuration

The main tool for managing the appearance of the data table is the Result Set Order/Filter Settings window.

To open this window, click the Custom Filters button () in the top toolbar of the editor or click the Configure button () and then click Order/Filter on the dropdown menu.

The Result Set Order/Filter Settings window provides tools to:

• Order data inside columns
• Manage the display of columns in the table
• Manage the order of columns in the table
• Filter data in the table using an SQL expression

Another tool for managing the data appearance is the column headers. In the data table, every column header contains three elements which each has its own function: Data (column) type icon, column name, filter icon, and ordering icon.

• By simply clicking the column name or column type, the icon highlights the whole column.
• You can click the column type icon and then drag and drop the column to a different position in the table.
• You can click the column name and then drag the cursor to the right or left to highlight multiple columns.
• Clicking the ordering icon allows you to order the data in the column in ascending or descending order – see the ‘Ordering Data in Columns’ section, further in this article

• Clicking the filter icon allows you to filter the data by a cell value, see [TBA]

Ordering Data in Columns

You can order data in columns in one of the following ways:

1. Click the ordering icon () in the header of the column.

   

   The icon has three states:

   • Clicking once establishes ascending order ()
   • Clicking a second time changes the order to descending ()
   • Clicking a third time removes the ordering from the column ()

To order data by several columns, go column by column, setting the order with the Ordering icon, starting from the column by which you want to order data first.

2. Click the Custom Filters button () in the top toolbar of the editor to open the Result Set Order/Filter Settings window (see above):

   a) Next to the column by which you want to order data in the first turn, set the ascending or descending order using the same three-state principle as described above.
   b) Set the ordering in other columns by which you want to sort the data in the second, third, etc. turn. The Order column indicates the order in which the sorting will happen.
   NOTE: The number (#) column indicates the initial order of columns.
   
   c) To easily move the ordering setting from column to column, you can use the Move up/down/to top/to bottom/ buttons:

To reset the data ordering to its initial state, click the Reset button () in the same window.

Also, to remove all ordering settings, click the Remove All Filters/Orderings button () in the top toolbar of the Data Editor.

Managing Display of Columns in Data Table

To hide a single column, right-click the column or any cell in it and click View/Format -> Hide column on the context menu. To unhide a hidden column, open the Result Set Order/Filter Settings window (see the image at the beginning of this article) and select the checkbox next to the column name, or click the Reset button ().

To display or hide columns in the data table, in the Result Set Order/Filter Settings window:

1. Select the checkboxes next to the columns that you want to see in the table and clear the checkboxes next to those that you want to hide.

   

2. Use the Show All () and Show None () buttons at the bottom of the window.

Sorting Columns in Data Table

You can modify the order of columns in the data table in two ways:

1. Click the icon in the column header and drag-and-drop the column to a new position.
2. To sort the column alphabetically, in the Result Set Order/Filter Settings window (open by clicking the Custom Filters button () in the top toolbar of the editor), click the Sort button ()
3. In the Result Set Order/Filter Settings window, click the column to set the focus to it and then move it using the navigation buttons: ()

Overview

Sometimes there are situations when you want to ignore the current primary key/unique value when importing into a table.

Some databases have syntax constructs in addition to the INSERT INTO that may help.

The choice of the replacement method is in the import settings – in “Data load settings”.

By default, the selection is

>, you can select other options from the drop-down list. The options available depend on the target database you are importing to.

The database can only support the replace method or the ignore method. In this case, the list of methods will consist of only one item except

>. If the base does not support the replacement methods, or if we have not added an implementation yet, then the combo with the list will be disabled.

Further, you will find a list of databases supporting these methods and examples of syntax.

Let’s take a look at an example of how this works. We use a small, simple, slightly-modified Sakila (MySQL) table – sakila.language

CREATE TABLE language_insert (

language_id tinyint unsigned NOT NULL,

name char(20) NOT NULL,

last_update timestamp NOT NULL,

PRIMARY KEY (language_id)

);

INSERT INTO sakila.language_insert (language_id,name,last_update) VALUES

(1,'English','2006-02-15 05:02:19.0'),

(2,'Italian','2006-02-15 05:02:19.0'),

(3,'Japanese','2006-02-15 05:02:19.0');

If we try to execute this request twice, we will get the following error: SQL Error [1062] [23000]: Duplicate entry ‘1’ for key ‘language_insert.PRIMARY’ (This message may look different in other databases).

Let’s take a new .csv file with the following content and try to use the replace methods.

"language_id","name","last_update"

1,Spanish,"2020-04-20 05:02:19.0"

2,Russian,"2020-05-20 05:02:19.0"

3,Belgian,"2020-06-20 05:02:19.0"

4,Mandarin,"2006-02-15 05:02:19.0"

5,French,"2006-02-15 05:02:19.0"

6,German,"2006-02-15 05:02:19.0"

If we set the “INSERT IGNORE” method in the settings, the result of the insert will look like this:

There will be no insertion errors, the first three lines will not change, and the fourth to sixth lines will be added to the table.

If we set the “REPLACE INTO” method in the settings, the result of the insert will look like this:

There will be no insertion errors, the first three lines will be replaced and the fourth to sixth lines will be added to the table.

Which databases support replace and insert methods?

MySQL

INSERT IGNORE and REPLACE INTO

Insert examples:

“INSERT IGNORE”

INSERT IGNORE INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0');

“REPLACE INTO”

REPLACE INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0');

SQLite

Documentation
“INSERT OR IGNORE” and “INSERT OR REPLACE”

Insert examples:

“INSERT OR IGNORE”

INSERT OR IGNORE INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0');

“INSERT OR REPLACE”

INSERT OR REPLACE INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0');

PostgreSQL

Available for PostgreSQL version 9.5.
“ON CONFLICT DO NOTHING” and “ON CONFLICT DO UPDATE SET”

Insert examples:

“ON CONFLICT DO NOTHING”

INSERT INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0') ON CONFLICT DO NOTHING;

“ON CONFLICT DO UPDATE SET”

INSERT INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0') 

ON CONFLICT (language_id) 

DO UPDATE SET (language_id, name, last_update) = (EXCLUDED.language_id, EXCLUDED.name, EXCLUDED.last_update);

FireBird

Available for FireBird version 2.1.
“UPDATE OR INSERT INTO”

Insert examples:

“UPDATE OR INSERT INTO”

UPDATE OR INSERT INTO language_insert(language_id, name, last_update) 

VALUES(1, 'English', '2006-02-15 05:02:19.0');

Oracle

Available for Oracle version 11.2.
“INSERT IGNORE ROW INDEX”

Insert examples:

“INSERT IGNORE ROW INDEX”

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(LANGUAGE_INSERT, LANGUAGE_INSERT_PK) */ 

INTO LANGUAGE_INSERT(LANGUAGE_ID, NAME, LAST_UPDATE) VALUES(1, 'English', TIMESTAMP '2006-02-15 05:02:19.0');

In the bottom left part of the Data editor, you can find the toolbar with actions:

Refreshing is necessary if the database contains changes made by other users working on it simultaneously with you, and you want to see them in your DBeaver window. To refresh data manually, right-click anywhere in the data table and click on the context menu or press F5.

Auto refresh

You can also schedule auto-refresh to happen regularly. To auto-refresh the database on schedule:

Click on the arrow in the auto-refresh button . Now you can configure auto refresh in the two following ways:

1 – Use a predetermined duration

You can select via choosing an available predetermined period in the context menu.

2 – Set your duration

You can set your time duration between refreshes. To do that, select .The Auto-refresh configuration dialog box will open:

a) Set Interval in seconds.
b) Select the Stop on error checkbox if you want the refresh to stop when it encounters an error or clear it if the refresh should ignore mistakes.
c) Click OK.

When you perform either of these two alternative options above, the system starts refreshing the data as scheduled, and the Configure auto-refresh button changes to the Stop auto-refresh button . To stop the auto-refresh, click the Stop auto-refresh button or the arrow next to it and click Stop on the auto-refresh menu.

To search for data in the result set, press CTRL+F. The standard Find/Replace search dialog box will open:

You can also use the Find and Replace feature.

NOTE: The system searches only in already fetched rows.

Setting	Description
Case sensitive	By default, the search is case insensitive.
Whole word	By default, the word specified in the search field can be found in the case when the word is part of another word. Enabling this setting will lead to a particular word search.
Regular expressions	Enable to use regular expressions in the search. In the search field, you can use Ctrl+Space for autocomplete regular expressions templates.
Wrap search	Enable this setting to find matches throughout the object from the beginning, and not only from the focus point to the end of the object.
Incremental	Matches are found incrementally as you type, if this setting enabled.