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

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_prefer开发者_高级运维ences" (
    "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.

Ruby solution:

gem install sequel

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

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.

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.

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.