countries-states-cities-database
countries-states-cities-database copied to clipboard
dr5hn
Working with sql/world.sql on postgres
I have made some adaptations on 'sql/world.sql' to work on postgres.
It seems to work. If you wish, I can fork the repo, perform more tests and offer a pull request.
Johann.
Hi @johannww,
That's awesome 🎉 Really appreciated it.
Since world.sql gets updated every time I fix the issues. Is there any way we can automate the conversion via Github action or script?
Moreover, please raise a PR and also if you can explain a process of conversion. I can easily figure out the way to automate it.
Thank you so much.
I attached my scripts world-conversion.zip. First, it is worth mentioning that I tried to use http://www.sqlines.com/, but the conversions were not successful.
Thus, I converted myself, part using sed and part using a python script.
Looking at the scripts, you will see that I erase things like:
ON UPDATE CURRENT_TIMESTAMP
AND
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT
I do not have much experience with databases, so I do not know the FULL consequences of changes like the second one. From what I researched, the first is very easy to solve with triggers. I also do not know if I properly dealt with \" escapes on the countries timezones, but I intend to look into it.
Using PostgreSQL 12.6, I could insert the data and perform a couple queries. Note that I wget the v1.3/sql/world.sql to deal with a stable version.
Right now I have other priorities, but I am willing to look more into it in a near future. I hope I helped you.
@johannww
Thanks, Mahn!! You're Awesome 🤩 This is really helpful. Gonna run some tests on the script and patch it with Github Actions.
Again Many Thanks.
I found a good source that can make your work easier, it works for most fields. But not for some.
https://sqlizer.io/