Geek asks and answers

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

I have a SQLite3 database. I made a data dump that 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 am trying to accomplish this in PostgreSQL PgAdmin III, which gives me a lot of errors, ranging from PRAGMA, to “unsigned” fields, to datetime fields to 1 instead of true and 0 instead of false.

Is there any way to convert this script?

I thought about exporting each table to CSV and then importing them into PGDB, but I have so many tables, this is not an option.

+3
source share
4 answers

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

SQL SQLiteman , SQL PG Admin III.

0

Ruby:

gem install sequel

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

, , SquirrelSQL.

JDBC sqlite3.

, . JDBC , .

, ; SQL.

0

, PRAGMA foregin_keys... - SQLite, PostgreSQL.
:

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;

pgAdmin III .

0

More articles:


All Articles