[sqlite] convert a PostgreSQL to sqlite

[Simon Slavin]

On 5 Jul 2015, at 1:44pm, 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?

Dump the Postgres database to a text file of SQL commands.  Then use the SQLite 
command-line tool to .read that text file.





There may be some minor differences in the two versions of SQL and you may have 
to deal with them before SQLite will .read your file.  If you're not sure of 
equivalents post here and we'll try to help.

Simon.

[sqlite] convert a PostgreSQL to sqlite

[Jim Callahan]

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 
wrote:

> On Sun, Jul 5, 2015 at 7:44 AM,  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   $sqlite3  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
>

[sqlite] convert a PostgreSQL to sqlite

[Jim Callahan]

Welcome to SQLite3!

Make sure SQLite3 is appropriate for your use; for some use cases a
client-server database like PostgreSQL is recommended:

"Situations Where A Client/Server RDBMS May Work Better
...If there are many client programs sending SQL to the same database over
a network, then use a client/server database engine [such as PostgreSQL]
instead of SQLite. SQLite will work over a network filesystem, but because
of the latency associated with most network filesystems, performance will
not be great.   ...A good rule of thumb is to avoid using SQLite in
situations where the same database will be accessed directly (without an
intervening application server) and simultaneously from many computers over
a network."
https://www.sqlite.org/whentouse.html

Assuming SQLite is appropriate/use scenario to your application; I agree
with Simon to use the SQL dump to transfer.

One area where SQL itself is not standardized is the syntax for testing for
NOT NULL.

According two the PostgreSQL documentation there are two ways to test for
NULL; SQLite will only accept the second method "NOTNULL"; the "IS NOT
NULL" syntax does not work.

PostgreSQL documentation:
"To check whether a value is or is not null, use the constructs:

expression IS NULLexpression IS NOT NULL

or the equivalent, but nonstandard, constructs:

expression ISNULLexpression NOTNULL

Do not write expression = NULL because NULL is not "equal to" NULL."
http://www.postgresql.org/docs/9.1/static/functions-comparison.html


I had used Microsoft Access, so I was used to the "IS NOT NULL" syntax and
had to learn "NOTNULL".

It  is not the end of the world, but it could be a gotcha.

Other than that one exception, whenever I have gotten the SQL correct
SQLite3 has worked for me.

HTH,
Jim Callahan
Orlando, FL

On Sun, Jul 5, 2015 at 4:11 PM, Simon Slavin  wrote:

>
> On 5 Jul 2015, at 1:44pm, 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?
>
> Dump the Postgres database to a text file of SQL commands.  Then use the
> SQLite command-line tool to .read that text file.
>
> 
> 
> 
>
> There may be some minor differences in the two versions of SQL and you may
> have to deal with them before SQLite will .read your file.  If you're not
> sure of equivalents post here and we'll try to help.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Slow real world performance - Any suggestions please (warning long)

[James K. Lowden]

On Fri, 3 Jul 2015 11:35:21 +0100
Rob Willett  wrote:

> It basically has taken no time to run 10,551 selects. This implies
> that we had issues on the database before with either fragmentation
> or indexes being poorly setup.  

Perhaps you said so and I missed it, but to me it's clear that almost
all the improvement comes from using RAG_Idx1.  Because it indexes the
Text column, you're now doing a binary search instead of a linear
scan.  

By changing the collation, you went from scanning every row to
searching a binary tree.  That took you from O(n) to O(log2 n).  If
each row in your 16 GB database is 100 bytes, allowing for overhead you
might have 80 million rows?  To satisfy your query, on average that
would be an improvement from 40,000,000 I/O operations to 13.  

When something that used to take 122 seconds starts going a million
times faster, it begins to look pretty much instantaneous.   :-)  

--jkl

[sqlite] convert a PostgreSQL to sqlite

[John McKown]

On Sun, Jul 5, 2015 at 7:44 AM,  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 <
John McKown

[sqlite] convert a PostgreSQL to sqlite

[c.bu...@posteo.jp]

Is there a way (free, open source, Ubuntu) to convert a
PostgreSQL-database with data in it to a sqlite database?