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 - 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
– Mel, Commented Jun 1, 2011 at 16:45
Possible duplicate of Convert SQLITE SQL dump file to POSTGRESQL — franklin
– franklin, Commented Mar 11, 2016 at 4:28

Mikhail Chuprynski · Accepted Answer · 2016-01-11 16:06:15Z

3

Ruby solution:

gem install sequel

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

answered Jan 11, 2016 at 16:06

Mikhail Chuprynski

2,5113 gold badges31 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Digulla · Accepted Answer · 2011-06-01 16:06:07Z

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 1, 2011 at 16:06

Aaron Digulla

330k111 gold badges626 silver badges840 bronze badges

1 Comment

darren Over a year ago

trying out squirrel now. i don't even know where to begin with the JDBC

the Tin Man · Accepted Answer · 2016-01-08 21:16:38Z

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 8, 2016 at 21:16

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

answered Jun 1, 2011 at 18:26

darren

19.4k17 gold badges62 silver badges81 bronze badges

Comments

the Tin Man · Accepted Answer · 2016-01-08 21:16:59Z

0

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.

edited Jan 8, 2016 at 21:16

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

answered Jun 1, 2011 at 16:06

mj82

5,2637 gold badges34 silver badges39 bronze badges

1 Comment

darren Over a year ago

my sql has over 15000lines, with many parts that dont' work like unsigned, datetime, etc...

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related