On 08/29/2014 07:40 AM, John McKown wrote:
> On Fri, Aug 29, 2014 at 9:06 AM, Kynn Jones <kynnjo@gmail.com> wrote:
>> 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.
>
> Read down in the man page for pg_dump. There are parameters such as
> --inserts and --column-inserts which will help. And you might want
> --quote-all-identifiers just in case some attribute (column name) is
> an SQLite key word.
>
Well I think the issue Kynn is referring to is Sqlites limited ability
to do ALTER TABLE. In a Postgres dump the basic structure of the table
is laid out using CREATE TABLE and then later ALTER TABLE commands are
used to finish adding the bells and whistles. Sqlite does not understand
those ALTER TABLE commands and fails on them. So to
get a dump to work you would need to create a complete CREATE TABLE
definition. The FOREIGN KEY ordering issue could be gotten around(I
believe) by toggling the foreign_keys PRAGMA in sqlite.
--
Adrian Klaver
adrian.klaver@aklaver.com
