Transforming pg_dump output to be compatible with SQLite 3.x

From: Kynn Jones <kynnjo(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Transforming pg_dump output to be compatible with SQLite 3.x
Date: 2014-08-29 14:06:21
Message-ID: CAFvQaj66kU9GQY_Yf4BAOy3uiAGJ+x8zThkPcjDWSrJi-Tb5WQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings!

I'm looking for tools/resources/ideas for making pg_dump's output
compatible with SQLite v. 3.1.3.

Ideally, I'd love to be able to do something like this (Unix):

% rm -f mydatabase.db
% pg_dump --no-owner --inserts mydatabase | pg_dump2sqlite3 | sqlite3
mydatabase.db

...where pg_dump2sqlite3 stands for some program (or pipeline) that
transforms the output of pg_dump as needed so that sqlite3 can digest it.

Among the tasks that the hypothetical pg_dump2sqlite3 program has to carry,
IMO the hardest one to implement is to compute the foreign-key dependencies
among the tables, and from this compute the sequential order in which the
tables will be created and populated[1].

Am I correct? Is there a way around this?

TIA!

kj

[1] In pg_dump's output, the sequential ordering of the CREATE TABLE
statements and of the COPY blocks that respectively define and populate the
tables does not take into account dependencies, because the specification
of these dependencies comes after all the CREATE TABLE and COPY commands,
in the form of ALTER TABLE statements. AFAIK, however, sqlite3 does not
allow adding foreign key constraints after the table has been created.
This means that both the ordering of table creation and population must
respect the dependencies among the tables.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John McKown 2014-08-29 14:40:18 Re: Transforming pg_dump output to be compatible with SQLite 3.x
Previous Message David G Johnston 2014-08-29 13:55:55 Re: Single Table Report With Calculated Column