How to convert SQLite3 script into script that PostgreSQL can understand?

Question

I have a SQLite3 database. I did a data dump which looks something like this:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "admin_tools_menu_bookmark" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL,
    "url" varchar(255) NOT NULL,
    "title" varchar(255) NOT NULL
);
INSERT INTO "admin_tools_menu_bookmark" VALUES(1,2,'/admin/recipes/recipe/','Recipe Management');
INSERT INTO "admin_tools_menu_bookmark" VALUES(2,2,'/admin/recipes/ingredient/','Ingredient Management');
CREATE TABLE "admin_tools_dashboard_preferences" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL,
    "data" text NOT NULL
);
......

I'm trying to execute this in PostgreSQL PgAdmin III, which gives me many many errors starting with PRAGMA, to 'unsigned' fields to datetime fields to 1 instead of true and 0 instead of false.

Is there a proper way to convert this script?

I thought of exporting each table to CSV then importing them into a PGDB but I have so many tables this isn't an option.

What did you use to create this dump? According to [this](http://www.sqlite.org/sqlite.html) - section #Converting An Entire Database To An ASCII Text File# - the '.dump' command creates an output that should be compatible (aka standard SQL) with other databases. — Mel, Jun 01 '11 at 16:45
Possible duplicate of [Convert SQLITE SQL dump file to POSTGRESQL](http://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql) — franklin, Mar 11 '16 at 04:28

score 3 · Answer 1 · answered Jan 11 '16 at 16:06

3

Ruby solution:

gem install sequel

sequel -C sqlite://db/development.sqlite3 postgres://user:password@localhost/dbname

answered Jan 11 '16 at 16:06

Mikhail Chuprynski

2,404
2
29
42

score 0 · Answer 2 · answered Jun 01 '11 at 16:06

0

Use a tool that can copy data between databases like SquirrelSQL.

There is a JDBC driver for sqlite3.

Note that this will still be a lot of manual work. The JDBC drivers will allow to copy the data but you still need to convert the table definitions manually.

Or you can write a tool that does simple string replacement; this usually works pretty well with SQL.

answered Jun 01 '11 at 16:06

Aaron Digulla

321,842
108
597
820

trying out squirrel now. i don't even know where to begin with the JDBC – darren Jun 01 '11 at 16:16

score 0 · Answer 3 · edited Jan 08 '16 at 21:16

I suppose PRAGMA foregin_keys... is some SQLite syntax which is not working in PostgreSQL.
Can you simply skip it and make:

BEGIN TRANSACTION;
CREATE TABLE "admin_tools_menu_bookmark" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL,
    "url" varchar(255) NOT NULL,
    "title" varchar(255) NOT NULL
);
INSERT INTO "admin_tools_menu_bookmark" VALUES(1,2,'/admin/recipes/recipe/','Recipe Management');
INSERT INTO "admin_tools_menu_bookmark" VALUES(2,2,'/admin/recipes/ingredient/','Ingredient Management');
CREATE TABLE "admin_tools_dashboard_preferences" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL,
    "data" text NOT NULL
);
COMMIT;

I tested it in my pgAdmin III and works.

my sql has over 15000lines, with many parts that dont' work like unsigned, datetime, etc... — darren, Jun 01 '11 at 16:10

score 0 · Accepted Answer · edited Jan 08 '16 at 21:16

0

I used a program called Navicat. I exported each table and then imported them into the new database, which was painful and slow.

The SQL dump using SQLiteman just didn't bode well when I put the SQL into PG Admin III.

edited Jan 08 '16 at 21:16

the Tin Man

158,662
42
215
303

answered Jun 01 '11 at 18:26

darren

18,845
17
60
79

How to convert SQLite3 script into script that PostgreSQL can understand?

4 Answers4

Linked