H
H
hbrmdc2016-08-21 22:31:20
PostgreSQL
hbrmdc, 2016-08-21 22:31:20

Postgres: Full-text search not looking on Debian for words with uppercase letters?

Postgres 9.5
Full text search works fine in test environment on my macbook. But on the VPS, it can't find any capitalized words. The problem is only with the Russian language.
This is a table created to index data for the purpose of full-text search:

CREATE TABLE public.tsv
(
  name character varying NOT NULL,
  id uuid NOT NULL,
  tsv tsvector NOT NULL,
  image boolean DEFAULT false,
  source uuid,
  "user" uuid,
  CONSTRAINT tsv_pkey PRIMARY KEY (id),
  CONSTRAINT source FOREIGN KEY (source)
      REFERENCES public.source (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tsv
  OWNER TO postgres;

This is an example of a query I'm doing on that table:
select id, name, image, type from tsv where tsv @@ to_tsquery('russian', 'кант') limit 18

On a macbook, as a result, I get what I need, namely this line from the database:
"Кант – Пол Стретерн";
"2779061c-3b58-4684-890c-cc24e8f5cdc0";
"'1724–1804 –':7 '«долга»':42 '«категорическ':44 'Аристотел':18 'Имману':5 'Кант':1A,6,24,46 'Платон':16 'Пол':3A 'Стрем':19 'Стретерн':4A 'Философ':41 'границ':21 'действительн':33 'императива»':45 'классическ':10 'крупн':13 'легкост':48 'лиш':35 'мыслител':14 'немецк':9 'определ':20 'опрокидыва':49 'основоположник':8 'позна':32 'познан':23 'получ':36 'представлен':40 'результат':54 'сам':12 'совершен':59 'спекулятивн':51 'стал':58 'субъективн':39 'теор':52 'указа':25 'философ':11,55 'человек':29 'человеческ':22 '–':2A";
"2779061c-3b58-4684-890c-cc24e8f5cdc0"

But exactly the same query does not find exactly the same row when the database is located in a Debian VPS. If I search for words indexed without capital letters - everything works ok, in English everything works ok.
This is the SQL pane of the working database:
CREATE DATABASE production_v2
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

And this one from the test one:
CREATE DATABASE test_database
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

I assume that Debian somehow does not correctly perceive upper case. How to fix it? The option "translate everything to lower case while indexing" is not suitable.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vladimir, 2016-08-22
@rostel

$ psql
=$ show all;
show that in the setting field the name fields with the values ​​lc_* for both instances

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question