Are you using an PostgreSQL add-ins such as "PostGIS" or an object relational mapper? In that case I would recommend an intermediate program or language.
For example, if you are using the PostGIS add-in I would recommend going through a GIS program such as ESRI's ArcGIS or the open source QGIS. There is a GIS addin for SQLite (Spatialite), but I would not trust a SQL to SQL transfer (PostgreSQL/PostGIS to SQLite/Spatialite) to transfer GIS information, since there is so much going on in the GIS above the level of SQL and there might be implementation differences (of equivalent GIS concepts) at the SQL level. Jim Callahan Orlando, FL On Sun, Jul 5, 2015 at 4:46 PM, John McKown <john.archie.mckown at gmail.com> wrote: > On Sun, Jul 5, 2015 at 7:44 AM, <c.buhtz at posteo.jp> wrote: > > > Is there a way (free, open source, Ubuntu) to convert a > > PostgreSQL-database with data in it to a sqlite database? > > > > If you're looking for something already done, I am not aware of anything > that will do that "out of the box" > > convertPGtoSQLite pgdatabase SQLite.db > > ?===? > > ?One way is to use pg_dump something like: > > pg_dump -C -c -a -f database-information.txt -O --column-inserts > --quote-all-identifiers --dbname=theDataBase > > You'd then need to _somehow_ (your choice), manipulate this file so that > you can feed it into the sqlite3 command. Or not. Just "for fun" (no > profit), I did the commands: > > $ pg_dump -C -c -n racf -f racf.info -O --column-inserts > --quote-all-identifiers joarmc > $ sqlite racf.db2 <racf.info > $sqlite3 <racf.info > Error: near line 5: near "SET": syntax error > Error: near line 6: near "SET": syntax error > Error: near line 7: near "SET": syntax error > Error: near line 8: near "SET": syntax error > Error: near line 9: near "SET": syntax error > Error: near line 10: near "SET": syntax error > Error: near line 16: near "SCHEMA": syntax error > Error: near line 23: near "COMMENT": syntax error > Error: near line 26: near "SET": syntax error > Error: near line 32: near "TYPE": syntax error > Error: near line 42: near "TYPE": syntax error > Error: near line 52: near "TYPE": syntax error > Error: near line 62: near "FUNCTION": syntax error > Error: near line 67: near "if": syntax error > Error: near line 68: near "if": syntax error > Error: near line 69: near "if": syntax error > Error: near line 70: near "if": syntax error > Error: near line 71: near "if": syntax error > Error: near line 72: near "return": syntax error > Error: near line 73: cannot commit - no transaction is active > Error: near line 74: near "$$": syntax error > Error: near line 81: near "FUNCTION": syntax error > Error: near line 86: near "if": syntax error > Error: near line 87: near "if": syntax error > Error: near line 88: near "if": syntax error > Error: near line 89: near "if": syntax error > Error: near line 90: near "if": syntax error > Error: near line 91: near "return": syntax error > Error: near line 92: cannot commit - no transaction is active > Error: near line 93: near "$$": syntax error > Error: near line 96: near "SET": syntax error > Error: near line 98: near "SET": syntax error > Error: near line 91437: near "USING": syntax error > ... > > Hum, not too shabby. > > > === > > A different approach would be to use some language, such as Perl (or > Python, Java, R, ...) with the appropriate PostgreSQL and SQLite drivers to > read the PostgreSQL data base and write it to ?SQLite. > > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
