Working with sql/world.sql on postgres

[johannww]

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.

[dr5hn]

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.

[johannww]

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.

[dr5hn]

@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.

[bregoh]

@dr5hn did you make postgresql work?

[dr5hn]

@bregoh No.. not yet maybe i will take up next week...

[dschinkel]

johannww
You save me a TON of work, thanks a TON. ⭐️

[dr5hn]

I found a good source that can make your work easier, it works for most fields. But not for some.

https://sqlizer.io/

[dr5hn]

#564

[dr5hn]

Postgre DB is now Added! Yay! 🎉