DB conversion: SQLite to PostgreSQL

Support
18

To summarize the solution:

1. Use pgloader tool to migrate Vikunja's SQLite DB to PostgreSQL

Link to pgloader: here

2. Convert all boolean DB columns in PG from bigint to boolean type

Here are the SQL scripts you need to execute on new PostgreSQL DB:

SQL that converts semantically boolean columns from bigint to boolean 
ALTER TABLE projects
    ALTER COLUMN is_archived DROP DEFAULT,
    ALTER COLUMN is_archived TYPE boolean USING is_archived != 0,
    ALTER COLUMN is_archived SET DEFAULT false;

ALTER TABLE tasks
    ALTER COLUMN done DROP DEFAULT,
    ALTER COLUMN done TYPE boolean USING done != 0,
    ALTER COLUMN done SET DEFAULT false;

ALTER TABLE saved_filters
    ALTER COLUMN is_favorite DROP DEFAULT,
    ALTER COLUMN is_favorite TYPE boolean USING is_favorite != 0,
    ALTER COLUMN is_favorite SET DEFAULT false;

ALTER TABLE teams
    ALTER COLUMN is_public DROP DEFAULT,
    ALTER COLUMN is_public TYPE boolean USING is_public != 0,
    ALTER COLUMN is_public SET DEFAULT false;

ALTER TABLE team_members
    ALTER COLUMN admin DROP DEFAULT,
    ALTER COLUMN admin TYPE boolean USING admin != 0,
    ALTER COLUMN admin SET DEFAULT false;

ALTER TABLE totp
    ALTER COLUMN enabled DROP DEFAULT,
    ALTER COLUMN enabled TYPE boolean USING enabled != 0,
    ALTER COLUMN enabled SET DEFAULT false;

ALTER TABLE users
    ALTER COLUMN email_reminders_enabled DROP DEFAULT,
    ALTER COLUMN email_reminders_enabled TYPE boolean USING email_reminders_enabled != 0,
    ALTER COLUMN email_reminders_enabled SET DEFAULT false;

ALTER TABLE users
    ALTER COLUMN discoverable_by_name DROP DEFAULT,
    ALTER COLUMN discoverable_by_name TYPE boolean USING discoverable_by_name != 0,
    ALTER COLUMN discoverable_by_name SET DEFAULT false;

ALTER TABLE users
    ALTER COLUMN discoverable_by_email DROP DEFAULT,
    ALTER COLUMN discoverable_by_email TYPE boolean USING discoverable_by_email != 0,
    ALTER COLUMN discoverable_by_email SET DEFAULT false;

ALTER TABLE users
    ALTER COLUMN overdue_tasks_reminders_enabled DROP DEFAULT,
    ALTER COLUMN overdue_tasks_reminders_enabled TYPE boolean USING overdue_tasks_reminders_enabled != 0,
    ALTER COLUMN overdue_tasks_reminders_enabled SET DEFAULT false;
Explanation

As far as I understand the problem is that SQLite defines boolean values (true/false) as numbers, while PG has specific boolean data type for that.

During conversion there’s no way for pgloader tool to understand if it should be boolean or number (bigint) so it defaults to bigint (basicaly leaves number as number).

Backend code fails to substitute/insert values in these columns because it passes true/false there while PG expects numbers.

During Oracle to PG conversion I had exactly the same problem, though the fix was a bit easier because our semantically boolean columns at least had the number length of 1, I don’t think it’s the case for SQLite which makes the distinction between boolean and number non-trivial for the conversion tool.

Note: new boolean columns might be added in the future which means if you know about them - adjust the SQL queries accordingly.

1 Like