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?

2
  • 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. Nov 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érace
    Nov 16, 2017 at 20:42

2 Answers 2

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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