How To Fix Encoding Error In Postgres

Posted on September 22, 2021 by bcat

Here is an example error when trying to create a database in Postgres:

ERROR 22023 (invalid_parameter_value) encoding "UTF8" does not match locale 
The chosen LC_CTYPE setting requires encoding "LATIN1".

Such an error might come up if OS locale was not set while installing Postgres. It is fixable by shuffling templates or by reinstalling Postgres after locale is set, I prefer the latest, cause having a fresh install just gives me more peace of mind. To fix that, we must first configure locale, on Debian/Ubuntu it would be something like that (the command will trigger an interactive prompt):

Continue reading →

Elixir Ecto: Wildcard search a Postgres JSONB field

Posted on August 22, 2018 by bcat

This seemingly straightforward task made me do some digging. Imagine we have a table with JSONB Field and we want to search for specific field values using wildcard character to allow partial matches.

Ecto doesn’t have a JSONB support but this particular task is so common that it’s even mentioned in the documentation:

fragment("?->>? ILIKE ?", p.map, "key_name", ^some_value)

What we need is just add the wildcard characters around the value. We can’t do that directly (“?->>? ILIKE %?%”) though since it would trigger Ecto’s syntax error and the query wouldn’t compile.
Continue reading →