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?
2
-
1Good on the transfer mechanism. None of your table names should be uppercased, then you have to quote them everywhere. The pg convention is for all table and column names to be lowercased.– Evan CarrollNov 16, 2017 at 19:00
-
PostgreSQL only takes account of case in table_names if you use quoted identifiers which is a typing nightmare! Just go with lower_case_with_underscores_they_are_cool and use UPPER CASE for SQL - everything is really easy to read/debug that way!– VéraceNov 16, 2017 at 20:42
Add a comment
|
2 Answers
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
