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_COLLATE
, LC_CTYPE
are the same, type en_US.utf8
, but LC_MESSAGES
and LC_NUMERIC
are en_US.utf8
on running well instance and C
on instance with issues. Then I've set last two to en_US.utf8
(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.
EDIT:
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.
en_US.utf8
which is a POSIX locale, not a Windows locale, you're probably in that case.