Migrating from SQLite to PostgreSQL Using Pentaho: A Practical Strategy for Slightly Different Schemas

While working on a project that required moving data from a legacy SQLite database to a modern PostgreSQL system, I encountered several challenges familiar to many developers: slight differences in schema definitions, type compatibility issues, and significant performance bottlenecks when dealing with large datasets. This article documents a methodical solution using Pentaho Data Integration (PDI) that enables clean, reproducible, and efficient data migration from SQLite to PostgreSQL.

Full Transformation Overview

Before diving into each step, here’s a high-level view of the entire transformation workflow:

Figure 1 — Overview of the complete Pentaho Data Integration transformation. The sequence includes: Table Input → Modified Javascript Value → Text File Output → Group By → Add Constants → Execute Row SQL Script. This layout separates reading, transforming, staging, and executing logic for optimal performance and maintainability.

Objective

The goal is to migrate user-related data from a SQLite table (users_user) to a PostgreSQL table with a similar but not identical schema. To improve flexibility and performance, the transformation generates SQL statements for each row and writes them to a file. These statements are then executed in bulk at the end of the process.

Database Connections

Set up two separate connections in Pentaho:

• SQLite connection: Points to the source .db file.
• PostgreSQL connection: Points to the target PostgreSQL database.

Step 1 — Read Data from SQLite

Use the Table Input step to extract data. To prevent type-related issues, ensure fields like dates are explicitly cast as strings:

SELECT
  printf('%s', birth_date) AS birth_date,
  password,
  last_login,
  is_superuser,
  username,
  first_name,
  last_name,
  email,
  is_staff,
  is_active,
  date_joined,
  id,
  bio,
  url,
  company,
  location,
  phone_number,
  notes,
  language
FROM users_user;

Using printf('%s', birth_date) ensures dates are returned as strings, avoiding issues like "Unable to get value 'Date' from database resultset".

Step 2 — Normalize and Convert Values with JavaScript

Add a Modified Javascript Value step to sanitize and format each field appropriately for PostgreSQL:

Figure 2 — This step converts each field from SQLite to a valid SQL string using helper functions. It handles nulls, strings, booleans, dates, and UUID formatting. The final SQL INSERT ... ON CONFLICT statement is assembled as a string and stored in the query field.

function safeUUID(id) {
  if (id === null || id === undefined) return "NULL";
  return "'" + id.replace(/^(.{8})(.{4})(.{4})(.{4})(.{12})$/, "$1-$2-$3-$4-$5") + "'";
}

function safeStr(val) {
  if (val === null || val === undefined) return "NULL";
  return "'" + String(val).replace(/'/g, "''") + "'";
}

function safeBoolean(val) {
  if (val === null || val === undefined) return "NULL";
  return Boolean(val);
}

function safeDate(val) {
  if (!val || typeof val !== "string" || !/^\d{4}-\d{2}-\d{2}/.test(val)) return "NULL";
  return "'" + val.replace(/'/g, "''") + "'";
}

var query =
  "INSERT INTO public.users_user " +
  "(birth_date, password, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined, id, bio, url, company, location, phone_number, notes, language) VALUES (" +
  safeDate(birth_date) + ", " +
  safeStr(password) + ", " +
  safeStr(last_login) + ", " +
  safeBoolean(is_superuser) + ", " +
  safeStr(username) + ", " +
  safeStr(first_name) + ", " +
  safeStr(last_name) + ", " +
  safeStr(email) + ", " +
  safeBoolean(is_staff) + ", " +
  safeBoolean(is_active) + ", " +
  safeStr(date_joined) + ", " +
  safeUUID(id) + ", " +
  safeStr(bio) + ", " +
  safeStr(url) + ", " +
  safeStr(company) + ", " +
  safeStr(location) + ", " +
  safeStr(phone_number) + ", " +
  safeStr(notes) + ", " +
  safeStr(language) +
  ") ON CONFLICT (id) DO UPDATE SET " +
  "password = EXCLUDED.password, " +
  "last_login = EXCLUDED.last_login, " +
  "is_superuser = EXCLUDED.is_superuser, " +
  "username = EXCLUDED.username, " +
  "first_name = EXCLUDED.first_name, " +
  "last_name = EXCLUDED.last_name, " +
  "email = EXCLUDED.email, " +
  "is_staff = EXCLUDED.is_staff, " +
  "is_active = EXCLUDED.is_active, " +
  "date_joined = EXCLUDED.date_joined, " +
  "bio = EXCLUDED.bio, " +
  "url = EXCLUDED.url, " +
  "company = EXCLUDED.company, " +
  "location = EXCLUDED.location, " +
  "phone_number = EXCLUDED.phone_number, " +
  "birth_date = EXCLUDED.birth_date, " +
  "notes = EXCLUDED.notes, " +
  "language = EXCLUDED.language;";

Only the query field should be retained as output unless debugging is required.

Step 3 — Write SQL to File (Batch Execution)

Use the Text File Output step to persist each query to a .sql file. Each line corresponds to a single SQL statement.

Settings:

• Filename: /opt/project/database_files/query
• Extension: sql
• Append: Unchecked
• Enclosure: Leave blank
• Fast data dump: Checked
• Field to write: query

Figure 3 — File tab configuration. Defines the target path for the SQL output file. All additional filename modifiers like step number or date are disabled to ensure a predictable and clean filename. “Add filenames to result” is also turned off.

Figure 4 — Content tab settings. “Fast data dump” is enabled for performance. Headers, footers, and enclosures are disabled to ensure raw SQL output. UTF-8 encoding and Unix line endings are applied.

Figure 5 — Fields tab configuration. Only the query field is selected. All formatting parameters such as length, precision, currency, or trimming are left blank to preserve the exact SQL string format.

Step 4 — Collapse to Single Row for Execution

Use a Group By step to reduce the stream to a single row. This avoids executing the SQL multiple times.

Settings:

• Group fields: Leave empty
• Include all rows?: Unchecked
• Always give back a result row: Checked
• Aggregates:
• Name: count
• Type: Number of rows (without field argument)

Figure 6 — Group By step configuration. With no group fields, all input rows are aggregated into one, allowing downstream steps to execute a single batch of SQL.

Step 5 — Define SQL File Path

Use Add Constants to define the absolute path to the .sql file generated previously.

Settings:

• Name: path
• Type: String
• Value: /opt/project/database_files/query.sql

Figure 7 — Add Constants step used to create a constant field named path, which is later referenced by the SQL execution step.

Step 6 — Execute SQL from File

Use the Execute row SQL script step to run the batch SQL stored in the file.

Settings:

• SQL field name: path
• Read SQL from file: Checked
• Execute for each row?: Unchecked

Figure 8 — Final step executes the .sql file by reading it from the path field. The script is run once, as a single transaction, improving performance and minimizing database overhead.

Conclusion

This method provides a clean and efficient solution for migrating data from SQLite to PostgreSQL, especially when schemas differ slightly. By separating transformation and execution, it allows better control, reduced memory usage, and more predictable runtime behavior.

Key benefits:

• Safe field transformation using custom JavaScript logic
• SQL output that can be reviewed, versioned, or reused
• Bulk execution that scales with dataset size

This approach can be applied to other database migrations with minimal adjustments and is particularly well-suited for ETL scenarios requiring repeatable and auditable workflows.

Appendix — Avoiding UUID Type Mismatch Errors in Pentaho

During early experimentation with direct Insert / Update steps in Pentaho (Table Input → Modified Javascript Value → Insert / Update), we encountered a PostgreSQL type mismatch error when handling UUID fields:

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

This issue arises because Pentaho’s default handling of UUIDs may pass them as character varying, while PostgreSQL expects a strict uuid type for indexed comparisons (especially in ON CONFLICT clauses or WHERE conditions). Unfortunately, this type discrepancy is not automatically cast by PostgreSQL.

Why We Avoided Insert / Update

To bypass this error and gain full control over data formatting and SQL syntax, we chose to generate raw SQL statements using JavaScript logic and batch execute them via file output. This allowed us to:

• Explicitly format UUIDs using a custom helper (safeUUID()).
• Avoid runtime casting errors.
• Maintain consistent and auditable SQL syntax.
• Circumvent Pentaho’s internal type guessing mechanism.

By decoupling transformation from execution and using the Execute row SQL script step instead of Insert / Update, we achieved both compatibility and reliability in a UUID-sensitive migration scenario.