1

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?

  • 1
    Good 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 Carroll Nov 16 '17 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érace Nov 16 '17 at 20:42
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.