Re: Transforming pg_dump output to be compatible with SQLite 3.x - Mailing list pgsql-general

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.

Example transcript:

pg_dump -c -t datedata -O -x --column-inserts --quote-all-identifiers
-d PostgreSQL_db | egrep -v '^SET '
--
-- PostgreSQL database dump
--

DROP TABLE "public"."datedata";

--
-- Name: datedata; Type: TABLE; Schema: public; Owner: -; Tablespace:
--

CREATE TABLE "datedata" (
    "id" "text",
    "date" "date",
    "value" "text"
);


--
-- Data for Name: datedata; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO "datedata" ("id", "date", "value") VALUES ('a', '2000-01-01', 'x');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('a', '2000-03-01', 'x');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('b', '2000-11-11', 'w');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-11-11', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-10-01', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-09-10', 'y');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-12-12', 'z');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('c', '2000-10-11', 'z');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('d', '2000-11-11', 'w');
INSERT INTO "datedata" ("id", "date", "value") VALUES ('d', '2000-11-10', 'w');


--
-- PostgreSQL database dump complete
--


--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown