0

I have a Postgres database server and I need to download the table records from postgres to tablet android (sqlite database). Is there a feature in Android to do this?

Example: There is a customer table in postgres and i need to import this table to sqlite in android.

Any tips are welcome.

Best regards.

Bidhan
  • 10,607
  • 3
  • 39
  • 50
Fabricio
  • 366
  • 6
  • 20

2 Answers2

3

This can be done by dumbing database in sql file from postgres and importing it to sqlite.

Dump database in a sql file.

pg_dump dbname > outfile.sql

Ref: http://www.postgresql.org/docs/9.1/static/backup-dump.html

Import database from sql dump file

sqlite> .read outfile.sql

Ref: https://www.sqlite.org/cli.html

You can dump specific table in postgres using following command:

pg_dump  --table=table dbname > outfile.sql
varnothing
  • 1,269
  • 1
  • 17
  • 30
  • That is a valid answer, but i need update the database if the record exists. – Fabricio May 20 '15 at 16:12
  • you can replace `INSERT INTO` with `INSERT OR REPLACE` in mysql dump file. http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace – varnothing May 22 '15 at 10:34
  • @Fabricio not agree. there are a lot of different details inside. you need to replace some true's ad false's. remove extra commands etc... check here more detailed review https://medium.com/@andreypu/how-to-port-postgresql-db-to-sqlite-db-e991b0d82287 – Kirguduck Dec 15 '21 at 15:28
0

...also you should clean your dump file before conversion
replace all true -> 't', false -> 'f'(but don't touch true and false in CREATE TABLEs)
remove all lines started with SET, SELECT, ALTER etc.
remove all mentions of SCHEMA. and OWNER
(sqlite does not understand it)
add BEGIN; as a first line and END; as - the last one
between them should stay only commands CREATE TABLE and your data
such as INSERT INTO
and check data types - SQLite does not understand BIG_INT (and not only him)

Kirguduck
  • 748
  • 1
  • 9
  • 20