Skip to content

Instantly share code, notes, and snippets.

@fiftin

fiftin/Convert PostgreSQL to SQLite

Created October 5, 2015 07:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save fiftin/18221054c7777e1f1207 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save fiftin/18221054c7777e1f1207 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. etlds revised this gist Jul 7, 2015. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Convert PostgreSQL to SQLite
    View file
    Original file line number Diff line number Diff line change
    @@ -21,6 +21,7 @@

    7. Import
    $ rm db/development.sqlite3
    $ rake db:migrate
    $ sqlite3 db/development.sqlite3
    > delete from schema_migrations;
    > .read dump.sql
  2. etlds revised this gist Jun 22, 2015. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion Convert PostgreSQL to SQLite
    View file
    Original file line number Diff line number Diff line change
    @@ -23,4 +23,5 @@
    $ rm db/development.sqlite3
    $ sqlite3 db/development.sqlite3
    > delete from schema_migrations;
    > .read dump.sql
    > .read dump.sql
    > .exit
  3. etlds revised this gist Jun 22, 2015. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Convert PostgreSQL to SQLite
    View file
    Original file line number Diff line number Diff line change
    @@ -20,6 +20,7 @@
    6. Add BEGIN; and END; to wrap the whole thing as a trasaction

    7. Import
    $ rm db/development.sqlite3
    $ sqlite3 db/development.sqlite3
    > delete from schema_migrations;
    > .read dump.sql
  4. etlds renamed this gist Jun 8, 2015. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → Convert PostgreSQL to SQLite
    View file
    File renamed without changes.
  5. etlds created this gist Jun 8, 2015.
    25 changes: 25 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    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
    $ sqlite3 db/development.sqlite3
    > delete from schema_migrations;
    > .read dump.sql