SQLite to PostgreSQL

SQLite is good for local database development but has not enough capabilities to be used as a long-term data warehouse even for small to medium size companies.

Migration from SQLite to PostgreSQL can be organized as the following steps:

Install PostgreSQL and create the target database
Extract SQLite schemas in form of DDL statements
Translate those DDL statements into PostgreSQL equivalents and load to the target database
Extract SQLite data into temporary storage like comma separate values (CSV) files
Apply necessary transformations to make it complied to PostgreSQL format
Load data into the PostgreSQL database
Convert SQL queries from SQLite to PostgreSQL

Schema Migration

The most straight forward method to migrate schemas from SQLite to PostgreSQL is to run the original DDLs statements in a PostgreSQL client tool and resolve failures one by one. For example, it can be done via psql command line client using the command line:

psql -h host -U user_name -d database_name -a -f script_file_name

Since SQLite only supports 5 data types (NULL, BLOB, INTEGER, TEXT and REAL), mapping types from SQLite to PostgreSQL does not require much efforts. Here are the most common syntax differences between SQLite and PostgreSQL.

AUTOINCREMENT becomes SERIAL.
Datetime columns become timestamp
BLOB columns become bytea
PostgreSQL does not support unsigned numeric types, so it must be converted into “integer CHECK (column_name > 0)”
SQLite boolean default values 0 and 1 must be explicitly casted to the type as 1::boolean and 0::boolean
Empty strings ” acting as NULL defaults values are converted into explicit NULLs
Distinguished sets of keywords. Names of SQLite entries that are PostgreSQL keywords must be escaped with double-quotes.

Data Migration

SQLite table can be exported into CSV format using the following command line:

sqlite3 -header -csv database_name “select * from table_name;” > table_name.csv

Then you can load the resulting CSV file into PostgreSQL via this command running from any client tool:

COPY table_name(coumn1, column2, …) FROM path_to_csv_file DELIMITER ‘,’ CSV HEADER;

BLOBs data is migrated through hexadecimal string representation. If you need it as binary bytea, the workaround is to load the string data into temporary table first and then convert it back into binary using PostgreSQL function decode(<hexadecimal string>, ‘hex’).

SQL Queries

Basically, syntaxes of queries in SQLite and PostgreSQL are similar. The following rules help you to convert queries from SQLite to PostgreSQL:

Functions extracting current date and time date(‘now’) and time(‘now’) is converted into PostgreSQL equivalents now()::date and now()::time
Date formatting function strftime is converted into PostgreSQL to_char, date part modifiers are similar in the two DBMS