You're correct. It is Friday leading to a 3 day weekend here. And it
is a short work day too. So my brain has definitely already left the
building. Thanks for pointing that out. I use SQLite some, but just
for very basic stuff and am not really familiar with it. Perhaps Kynn
could show what, in particular, is causing his problem(s).
On Fri, Aug 29, 2014 at 9:58 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> 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
--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan
Maranatha! <><
John McKown
