I can use pgloader to transfer my sqlite database to postgres as described in this quickstart page.
But the result is all my tables' names are lowercases. e.g. TestName -> testname
Is there any way I can preserve the uppercase in my table names?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
In SQLite, table names always are case-insensitive (even when quoted).
In PostgreSQL, unquoted identifiers are folded to lower case, but then the search for the table is done case-sensitively. So the only way to get the same behaviour as in SQLite queries is to use unquoted names, which implies that the actual names must be lower case.
In other words: the table name must be
testname in order for a query like
SELECT * FROM TestName to work correctly.
pgloader provides a new option
quote identifiers, and I just tested using the following command, it worked like a charm:
load database from sqlite://path/to/sqlite.db into postgresql://postsql-connection-string with include drop, quote identifiers, create tables, create indexes, reset sequences set work_mem to '16MB', maintenance_work_mem to '512 MB';
See this discussion for more details: https://github.com/dimitri/pgloader/issues/476#issuecomment-260458985