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.

  • 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

SQLite to PostgreSQL Converter

The previous sections of this white paper prove that database migration from SQLite to PostgreSQL is a tedious process that can take mush efforts when doing manually. That is why database professionals use special software to automate some parts or overall migration.

SQLite to PostgreSQL converter is one of such tools developed by Intelligent Converters software company. The product can automate database migration via user friendly wizard-style interface. Here are the key features of the SQLite to PostgreSQL converter:

  • Support for all modern versions of SQLite and PostgreSQL
  • Works with both on-premises and cloud PostgreSQL databases
  • Schemas, data, indexes, constraints and views are migrated
  • High performance migration due to low-level techniques of reading and writing data
  • Command line support
  • Option to store migration parameters into profile to simplify next run

Visit official site of the vendor to learn more about SQLite to PostgreSQL converter.

Leave a Reply

Your email address will not be published. Required fields are marked *