Convert PostgreSQL to SQLite

Convert PostgreSQL to SQLite

1. Dump the data only sql to file
  $ pg_dump --data-only --inserts YOUR_DB_NAME > dump.sql
2. scp to local

3. Remove the SET statements at the top
  such as:
    SET statement_timeout = 0;
    SET client_encoding = 'SQL_ASCII';

4. Remove the setval sequence queries
  such as: 
    SELECT pg_catalog.setval('MY_OBJECT_id_seq', 10, true);
    
5. Replace true => ‘t’ and false => ‘f’
  -- These:
  INSERT INTO table_name VALUES (1, true, false);
  -- Should be replace to:
  INSERT INTO table_name VALUES (1, 't', 'f');

6. Add BEGIN; and END; to wrap the whole thing as a trasaction

7. Import
  $ rm db/development.sqlite3
  $ rake db:migrate
  $ sqlite3 db/development.sqlite3
  > delete from schema_migrations;
  > .read dump.sql
  > .exit

This was very helpful but may be out of date now.
I found I had to remove "public" prefix from table names, and instead of "t"/"f" I could use true/false values per the export.

Not sure if it was just my sequence but also performed:
delete from ar_internal_metadata;

(along with delete from schema_migrations;)

sqlite> .version
SQLite 3.39.5 2022-10-14 20:58:05 554764a6e721fab307c63a4f98cd958c8428a5d9d8edfde951858d6fd02daapl
clang-14.0.3

Just to wrap this up I had to change some postgresql JSONB fields to JSON, and some Integer/String Array fields to JSON fields.

I also had to use different date calculation queries using sqlite's JULIANDAY and the TRIM functions were different in my search/filter queries and indexes. But that was pretty much it!