I have a database on my development machine where everything runs just fine.
I copied the database to the server using file system level backup. Most queries run fine and fast. But I got problems with one table.
If I query data and sort by pk "id", then it returns all rows without any issues. But if I sort by column "label" which is text type without empty cells, then I got an error:
SQL Error [XX000]: ERROR: could not compare Unicode strings: Invalid argument
I tried to compare LC_COLLATE, LC_CTYPE, LC_MESSAGES and LC_NUMERIC on both machines.
LC_CTYPE are the same, type
en_US.utf8 on running well instance and
C on instance with issues. Then I've set last two to
SET lc_messages TO 'en_US.utf8';) but this didn't help.
There are no special symbols in that table, just normal plain text ASCII symbols. I tried to delete all rows and added them again - no change.
I do not understand the source of the problem. Suspect localization issues.
Postgres version where I experience the issue: v.13.5 (windows x64). in the postgresql.conf locale settings are:
# These settings are initialized by initdb, but they can be changed. lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting
If I change "C" to "en_US.utf8" then the database fails to start.
Select query which returns without errors:
SELECT * FROM my_schema.my_table ORDER BY id ASC;
Query returning an error:
SELECT * FROM my_schema.my_table ORDER BY label ASC;
label is non-reserved key-word in postgresql according to docs.
Column id is primary key, type serial4, "label" column is of type text. No null values in both columns.