Converting Postgresql SQL files to SQLite

Converting Postgresql SQL files to SQLite.md

Step 1: Dump your postgresql create_table statements to a file:

pg_dump -s my_db_name > my_db_name_create.sql

• You will need to manually remove all statements except CREATE TABLE, CREATE INDEX, CREATE_UNIQUE INDEX, etc.
  • Honestly just do it. It wont take too long, you'll be fine.
  • Some statements you will definately need to remove are SET, ALTER, CREATE SEQUENCE
  • For INDEX statements we need to find-and-remove USING btree
• Use find-and-replace to remove all schema table name prefixes. For example remove all instances of public.
• INDEX statements we need to find-and-remove USING btree
• Fix the column datatypes
  • For all id columns, append " PRIMARY KEY" to the line.
  • Find-and-replace all boolean DEFAULT false with boolean DEFAULT 0
  • Find-and-replace all boolean DEFAULT true with boolean DEFAULT 1
  • Find-and-replace all occurences of timestamp without time zone and timestamp([0-9]) without time zone with datetime
  • Manually fix all occurences of :: in your column defaults or whatever. This syntax is not compatible with sqlite

Step 2: Dump your postgresql data/insert statements to a file:

pg_dump --data-only --inserts my_db_name > my_db_name_inserts.sql

• You will need to manually remove all statements except INSERT INTO
  • Honestly just do it. It wont take too long, you'll be fine.
  • Some statements you will definately need to remove are SET, SELECT pg_catalog
• Use find-and-replace to remove all schema table name prefixes. Find INSERT INTO public. and replace with "INSERT INTO "

Step 3: Create your SQLite Database file using the SQLite console

Run the sqlite3 my_db.sqlite.db command in your terminal

Run the following commands in the sqlite console. Its possible there may be no feedback to the .read commands this is normal. Otherwise it may show some errors or warnings which might be helpful if you did some bad find and replace or missing some more manual conversion.

sqlite> .read my_db_name_create.sql
sqlite> .read my_db_name_inserts.sql

Now you should perform a SELECT statement to ensure the success of the operations

sqlite> SELECT count(*) FROM some_table;
9123
sqlite> SELECT * FROM some_table LIMIT 1;

After this you can close your console and you should now have a working sqlite3 copy of you postgres database in the my_db.sqlite.db file