Convert SQLITE SQL dump file to POSTGRESQL

by

You should be able to feed that dump file straight into psql:

/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql

If you want the id column to “auto increment” then change its type from “int” to “serial” in the table creation line. PostgreSQL will then attach a sequence to that column so that INSERTs with NULL ids will be automatically assigned the next available value. PostgreSQL will also not recognize AUTOINCREMENT commands, so these need to be removed.

You’ll also want to check for datetime columns in the SQLite schema and change them to timestamp for PostgreSQL. (Thanks to Clay for pointing this out.)

If you have booleans in your SQLite then you could convert 1 and 0 to 1::boolean and 0::boolean (respectively) or you could change the boolean column to an integer in the schema section of the dump and then fix them up by hand inside PostgreSQL after the import.

If you have BLOBs in your SQLite then you’ll want to adjust the schema to use bytea. You’ll probably need to mix in some decode calls as well. Writing a quick’n’dirty copier in your favorite language might be easier than mangling the SQL if you a lot of BLOBs to deal with though.

As usual, if you have foreign keys then you’ll probably want to look into set constraints all deferred to avoid insert ordering problems, placing the command inside the BEGIN/COMMIT pair.

Thanks to Nicolas Riley for the boolean, blob, and constraints notes.

If you have ` on your code, as generated by some SQLite3 clients, you need to remove them.

PostGRESQL also doesn’t recognize unsigned columns, so you might want to drop that or add a custom-made constraint such as this:

CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);

While SQLite defaults null values to '', PostgreSQL requires them to be set as NULL.

The syntax in the SQLite dump file appears to be mostly compatible with PostgreSQL so you can patch a few things and feed it to psql. Importing a big pile of data through SQL INSERTs might take a while but it’ll work.

More Related Contents:

  1. Version of SQLite used in Android?
  2. Copying PostgreSQL database to another server
  3. What are the performance characteristics of sqlite with very large database files? [closed]
  4. Primary keys with Apache Spark
  5. How can I put a database under git (version control)?
  6. How to retrieve the last autoincremented ID from a SQLite table?
  7. How to recover a corrupt SQLite3 database?
  8. How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  9. Change from SQLite to PostgreSQL in a fresh Rails project
  10. Store query result in a variable using in PL/pgSQL
  11. Can I rollback a transaction I’ve already committed? (data loss)
  12. Kill a postgresql session/connection
  13. SQLite UPSERT / UPDATE OR INSERT
  14. How to backup sqlite database?
  15. Import SQL dump into PostgreSQL database
  16. SQLite insert speed slows as number of records increases due to an index
  17. how to use Blob datatype in Postgres
  18. How can I solve Postgresql SCRAM authentication problem?
  19. What’s the best way to migrate a Django DB from SQLite to MySQL?
  20. How to login and authenticate to Postgresql after a fresh install?
  21. Sqlite in flutter, how database assets work
  22. Android: Where are database files stored?
  23. Can I download an SQLite db on /sdcard and access it from my Android app?
  24. Populate Android Database From CSV file?
  25. Populating Spinner From SQLite Database Android
  26. Adding a new value to an existing ENUM Type
  27. How can I import a .sql file into my Heroku postgres database?
  28. Maximum number of rows in a sqlite table
  29. Predict next auto-inserted row id (SQLite)
  30. Backup/Restore a dockerized PostgreSQL database

Leave a Comment