0

Just a little conundrum I have faced with.

I have a table player with login column(varchar(255)). The rows are inserted by Java code.

I've a record with id = 173 login = PLN2test:
When I search for this record by the following query,

SELECT login FROM player WHERE login = 'PLN2test'

or even:
SELECT login FROM player WHERE login = (SELECT login FROM player WHERE id = 173)

I get zero results!

However when I try:
SELECT login FROM player WHERE login LIKE '%PLN2test'

or
SELECT login FROM player WHERE login LIKE 'PLN2test%'

I get my record.

Please can you tell me why this is happening?
Is there known bug on the postgres side?
How I can debug query execution and understand why it returns unexpected result?

P.S. SELECT length(login) FROM player WHERE id = 173 gives me 8

show lc_collate;
 lc_collate 
------------
 en_US.utf8
(1 row)

show client_encoding ;
 client_encoding 
-----------------
 UTF8
(1 row)

OS version: Ubuntu 20.04

It looks like colleague of mine just recently changed a collate type from C.UTF-8 to en_US.utf8). Can this lead to such a behavior? And how to migrate data to new type now?

Improve this question
8
  • A) If there is an index on the field try reindexing B) It is an encoding issue. What does show lc_collate ; and show client_encoding ; in psql return? What encoding is the Java code using? Add answers as update to your question. Also what OS and version and have there been any OS updates lately? Again add to your question. Commented Feb 2, 2022 at 17:16
  • @Adrian Klaver There is no index on this field. I've updated my question. Commented Feb 2, 2022 at 17:25
  • The update does not include the Java encoding or the OS information. Commented Feb 2, 2022 at 17:31
  • @Adrian Klaver looks like colleague of mine just recently changed a collate type(from C.UTF-8 to en_US.utf8), can this lead to such a behavior? And how to migrate data to new type now? Commented Feb 2, 2022 at 17:41
  • 1
    What you have found is that you should not go around changing system catalogs(pg_database) manually. Per Locale: "Some locale categories must have their values fixed when the database is created. You can use different settings for different databases, but once a database is created, you cannot change them for that database anymore. LC_COLLATE and LC_CTYPE are these categories." Commented Feb 2, 2022 at 18:50

1 Answer 1

Reset to default
2

What you have found is that you should not go around changing system catalogs(pg_database) manually. Per Locale: "Some locale categories must have their values fixed when the database is created. You can use different settings for different databases, but once a database is created, you cannot change them for that database anymore. LC_COLLATE and LC_CTYPE are these categories."

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.