Postgresql full text search + product model search?

Question

PostgreSQL

Greetings. Prompt, please, on a subject.
There is a full-text search for information pages that contain product models, for example, DG1875, DG1885, etc.
How to make sure that when entering a numeric part, the pages fall into the selection. In the manual, I smoked only the search for the prefix to_tsquery('prefix:*'). Do not poke like in where :)

Answer 1

Do you have fixed length prefixes? If yes, then you can try this method. Only the numeric part can be put in the index:

=# create table test (t text);
=# insert into test values ('DG1875'),('DG1885');
=# create index on test (cast(substr(t, 3) as int));
=# select * from test where cast(substr(t, 3) as int) = 1875;
   t    
--------
 DG1875
=# explain analyze select * from test where cast(substr(t, 3) as int) = 1875;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_substr_idx1 on test  (cost=0.13..8.14 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1)
   Index Cond: ((substr(t, 3))::integer = 1875)
 Planning time: 0.142 ms
 Execution time: 0.057 ms

If variable length, then you can remove the prefix using ltrim:

=# select ltrim('ABC1875', 'ABC');
 ltrim
-------
 1875