将SQLITE SQL转储文件转换为POSTGRESQL [英] Convert SQLITE SQL dump file to POSTGRESQL

问题描述

我一直在使用SQLITE数据库进行开发，并在POSTGRESQL中进行生产。

基于运行 sqlite数据库，我们只需要将一个特定的表传递给生产数据库。 .dump> /the/path/to/sqlite-dumpfile.sql ，SQLITE以以下格式输出表转储：

  BEGIN交易; 
 CREATE TABLEcourses_school（idinteger PRIMARY KEY，department_countinteger NOT NULL DEFAULT 0，the_idinteger UNIQUE，school_namevarchar（150），slugvarchar（50） 
 INSERT INTOcourses_schoolVALUES（1,168,213，'TEST Name A'，NULL）; 
 INSERT INTOcourses_schoolVALUES（2,0,656，'TEST Name B'，NULL）; 
 .... 
 COMMIT; 
 

如何将上述内容转换为可导入到生产服务器的POSTGRESQL兼容转储文件？

解决方案

您应该能够将该转储文件直接送入 psql ：

  / path / to / psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql 
  

如果您希望 id 列更改为自动递增，然后在表创建行中将其类型从int更改为serial。 PostgreSQL然后将一个序列附加到该列，以便具有NULL ID的INSERT将被自动分配下一个可用的值。 PostgreSQL也不会识别 AUTOINCREMENT 命令，因此这些命令需要删除。

在SQLite模式中为 datetime 列并将它们更改为 timestamp 为PostgreSQL（感谢留言指出这一点）。

如果你的SQLite中有布尔值，那么你可以转换 1 和 0 和 1 :: boolean c $ c> 0 :: boolean （分别）或者你可以在转储的模式部分中将布尔值更改为整数，然后在导入后在PostgreSQL中手动修复它们。 p>

如果您的SQLite中有BLOB，那么您需要调整模式以使用 bytea 。您可能需要混用一些 解码来电 。用你最喜欢的语言写一个quick'n'dirty复印机可能比修改SQL更容易，如果你有很多BLOB处理。

像往常一样，如果你有外键，那么您可能需要查看 set constraints all deferred 避免插入顺序问题，将命令放在BEGIN / COMMIT对中。

感谢 Nicolas Riley 处理布尔，blob和约束注释。

如果您的代码上有`，由一些SQLite3客户端生成，您需要删除它们。

PostGRESQL也无法识别无符号列，您可能要删除它，或添加一个自定义的约束，例如：

  CREATE TABLE tablename（
 ... 
 unsigned_column_name integer CHECK（unsigned_column_name> 0）
）; 
 

虽然SQLite默认值为'' ，PostgreSQL要求它们被设置为 NULL 。

SQLite转储文件中的语法似乎与PostgreSQL大部分兼容，因此您可以修补几个内容并将其提供给 psql 。通过SQL INSERT导入大量数据可能需要一段时间，但它会工作。

I've been doing development using SQLITE database with production in POSTGRESQL. I just updated my local database with a huge amount of data and need to transfer a specific table to the production database.

Based on running sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE outputs a table dump in the following format:

BEGIN TRANSACTION;
CREATE TABLE "courses_school" ("id" integer PRIMARY KEY, "department_count" integer NOT NULL DEFAULT 0, "the_id" integer UNIQUE, "school_name" varchar(150), "slug" varchar(50));
INSERT INTO "courses_school" VALUES(1,168,213,'TEST Name A',NULL);
INSERT INTO "courses_school" VALUES(2,0,656,'TEST Name B',NULL);
....
COMMIT;

How do I convert the above into a POSTGRESQL compatible dump file that I can import into my production server?

解决方案

You should be able to feed that dump file straight into psql:

/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql

If you want the id column to "auto increment" then change its type from "int" to "serial" in the table creation line. PostgreSQL will then attach a sequence to that column so that INSERTs with NULL ids will be automatically assigned the next available value. PostgreSQL will also not recognize AUTOINCREMENT commands, so these need to be removed.

You'll also want to check for datetime columns in the SQLite schema and change them to timestamp for PostgreSQL (thanks to Clay for pointing this out).

If you have booleans in your SQLite then you could convert 1 and 0 and 1::boolean and 0::boolean (respectively) or you could change the boolean column to an integer in the schema section of the dump and then fix them up by hand inside PostgreSQL after the import.

If you have BLOBs in your SQLite then you'll want to adjust the schema to use bytea. You'll probably need to mix in some decode calls as well. Writing a quick'n'dirty copier in your favorite language might be easier than mangling the SQL if you a lot of BLOBs to deal with though.

As usual, if you have foreign keys then you'll probably want to look into set constraints all deferred to avoid insert ordering problems, placing the command inside the BEGIN/COMMIT pair.

Thanks to Nicolas Riley for the boolean, blob, and constraints notes.

If you have ` on your code, as generated by some SQLite3 clients, you need to remove them.

PostGRESQL also doesn't recognize unsigned columns, you might want to drop that, or add a custom-made constraint such as this:

CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);

While SQLite defaults null values to '', PostgreSQL requires them to be set as NULL.

The syntax in the SQLite dump file appears to be mostly compatible with PostgreSQL so you can patch a few things and feed it to psql. Importing a big pile of data through SQL INSERTs might take awhile but it'll work.

这篇关于将SQLITE SQL转储文件转换为POSTGRESQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！