How to Fix DBeaver’s Over Escaping Issue When Copying JSONB Data in SQL Inserts

If you’ve ever used DBeaver to clone database records containing JSONB data in sql, you might have encountered a frustrating problem: the tool adds unnecessary escape characters (like turning \r\n into \\r\\n) when generating INSERT statements. This over-escaping breaks the integrity of structured data, forcing you to manually fix the SQL every time. I’ll explain why this happens and walk you through a simple fix to save your sanity.

Over-Escaped JSONB Data

Let’s say you have a reports table with a JSONB-style text column. When you query a row (e.g., SELECT * FROM reports WHERE id = 1) and use DBeaver’s Generate SQL > Insert feature, the tool generates code like this:

"select_logic": "komm = %w(kommunikation).map do |attribut|\\r\\n  ..."

But the correct value should be:

"select_logic": "komm = %w(kommunikation).map do |attribut|\r\n  ..."

Notice the extra backslashes? These are added by DBeaver to “escape” existing backslashes, which corrupts the JSONB structure. The result? Your copied data becomes invalid unless you manually remove the extra escapes.

Why Does This Happen?

DBeaver’s Generate SQL feature is designed to ensure strings are safely escaped for SQL execution. For example, single quotes (') are converted to '' or \', and backslashes (\) become \\ by default. This is helpful for plain text but problematic for pre-formatted JSONB strings that already include escape sequences (like \r\n for line breaks). DBeaver treats these as literal backslashes needing escaping, resulting in \\r\\n.

Disable Backslash Escaping in DBeaver

The solution lies in adjusting DBeaver’s SQL generation settings. Here’s how:

1. Open Preferences:
   • Go to DBeaver > Preferences (macOS) or Window > Preferences (Windows/Linux).
2. Navigate to SQL Settings:
   • Expand Editors > SQL Editor and select SQL Processing.
3. Disable Backslash Escaping:
   • Uncheck the option: Escape backslash in strings when pasting or generating SQL.
4. Apply Changes:
   • Click Apply and Close.

Now, regenerate your INSERT statement. The JSONB data should retain its original escape characters (e.g., \r\n instead of \\r\\n).

Alternative Workarounds

If the setting above doesn’t resolve the issue (or isn’t available in your DBeaver version), try these alternatives:

Manual SQL Construction

Write a custom query to generate the INSERT statement without relying on DBeaver’s tool:

SELECT 
  'INSERT INTO reports (jsonb_column) VALUES (' || 
  quote_literal(jsonb_column) || 
  ');' AS insert_statement 
FROM reports 
WHERE id = 1;

The quote_literal() function handles quote escaping without over-processing backslashes.

Use a Script

Automate copying records with a stored procedure:

CREATE OR REPLACE FUNCTION copy_report(source_id INT) 
RETURNS VOID AS $$
  INSERT INTO reports (jsonb_column)
  SELECT jsonb_column FROM reports WHERE id = source_id;
$$ LANGUAGE SQL;

Call it with SELECT copy_report(1); to duplicate the report.

Export and Re-Import Data

Export the record as CSV (with proper escaping) and re-import it. This bypasses DBeaver’s SQL generator entirely.

Final Thoughts

DBeaver is a powerful tool, but its default escaping behavior can clash with structured data like JSONB. Disabling the Escape backslash setting is the most straightforward fix, but scripting or manual queries offer flexibility for complex use cases. Whichever method you choose, the goal is the same: spend less time fixing escapes and more time building features.

Pro tip: Always validate JSONB data after insertion to ensure escapes are preserved. Tools like jsonb_valid() in PostgreSQL can help automate checks.