2

Need to import in Linux a Windows postgres exported database. In windows it's listed as follows:

    Name     |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-------------+----------+----------+--------------------+--------------------+-----------------------
   the_db    | postgres | UTF8     | Hebrew_Israel.1255 | Hebrew_Israel.1255 |

Do I really need to create the database using the same collate and ctype before restoring? I already tested, and I can import just by creating a new database which will make it using its defaults:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 the_db    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  • Restoring was successful. Hoever, I am afraid because of the application, not to cause any errors when interacting with the db later.
  • What are your consideration gentleman?
1

The main consideration is that if you use ORDER BY with a column of a string data type, the result will be sorted according to the database collation unless you specify an explicit COLLATE clause for the column definition or with the ORDER BY. If your users expect the results sorted according to Hebrew sorting rules, they might be unhappy.

To get a Hebrew collation on Unix, use the locale he_IL.utf8.

3
  • I've tried to create it using "create database test WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'Hebrew_Israel.1255' LC_CTYPE = 'Hebrew_Israel.1255';" however I end up with "ERROR: invalid locale name: "Hebrew_Israel.1255"". Not really sure how to solve it...and I googled it for a while ..
    – catalin
    Nov 17 '20 at 12:48
  • 1
    You could try he_IL.utf8, as noted in my extended answer. Nov 17 '20 at 15:25
  • indeed "create database the_db with template=template0 encoding='utf8' lc_collate='he_IL.utf8' lc_ctype='he_IL.utf8' owner=postgres;" makes it work. Hebrew_Israel.1255 is the collation for windows. thanks!
    – catalin
    Nov 17 '20 at 16:12

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.