December 30th, 2014
In Technology
No Comments
If you enjoy this article, see the other most popular articles
If you enjoy this article, see the other most popular articles
If you enjoy this article, see the other most popular articles
How to convert Sqlite to Postgres
(written by lawrence krubner, however indented passages are often quotes). You can contact lawrence at: lawrence@krubner.com, or follow me on Twitter.
I spent some hours trying to convert a Sqlite database to Postgres, and in the end, I failed. Or rather, I decided that it was no longer worth the time. I thought this would cost me 30 minutes, but hours went by and I was unable to make progress. For those using Ruby On Rails there is a good article about how to do it. I was not using any framework. Some of the problems that I ran into:
1.) syntax differences between Sqlite and Postgres
2.) foreign key constraints (I had to turn them all off)
3.) non-standard data in columns, such as:
INSERT INTO ariel (id,created,modified,draft,_data,has_flags,private,deleted,block_id,pregnancy_id,progress_note_id) VALUES (6726,’2014-11-28 13:55:12.194549′,’2014-11-28 13:55:12.194573′,0,”,0,0,1,’demographic.personal.culture’,262,null);
ERROR: invalid input syntax for type json
LINE 1: …8 13:55:12.194549′,’2014-11-28 13:55:12.194573′,0,”,0,0,1,’…
That should be ‘{}’ because the field is jsonb.
Also:
INSERT INTO todos (id,created,modified,draft,deleted,content,completed,position,type,checked_on,checked_by_id,todo_list_id,user_id,client_id,practice_id,progress_note_id) VALUES (‘2173′,’2014-10-07 15:05:37.592417′,’2014-10-07 15:05:37.592445′,’0′,’0′,’small pox vaccine’,’null’,’10’,’item’,’null’,’null’,’53’,’null’,’null’,’null’,’null’);
ERROR: invalid input syntax for integer: “null”
LINE 1: …05:37.592445′,’0′,’0′,’small pox vaccine’,’null’,’10…
These kinds of errors are just endless. Trying to come up with good default types for every column in every table is way behind what I am willing to do. I initially thought converting Sqlite to Postgres would take me 30 minutes, but now I think it takes some serious set up, and goes best when you have a big framework like Ruby On Rails helping with a good ORM.
Post external references
- 1
http://blog.flatironschool.com/post/68064669290/porting-sqlite3-data-to-postgresql-database-in
February 8, 2022 9:33 am
From Michael S on How I recovered from Lyme Disease: I fasted for two weeks, no food, just water
"Did you have Bartonella, too? Seems it uses autogenesis..."