CODEWITHSUNDEEP
sqldatabasepostgresql
Вячеслав
Вячеслав

Reputation: 21

Error adding a string with a colon ":" to the "tsvector" data type

In my PostgreSQL 11 database, there is a "name" column with the "tsvector" data type for implementing full-text search.

But when I try to add to this column an ​​entry containing a colon ":", an error occurs:

Exception in thread Thread-10:
Traceback (most recent call last):
  File "C:\Program Files\Python37\lib\threading.py", line 917, in_bootstrap_inner
    self.run()
  File "C:\Program Files\Python37\lib\threading.py", line 865, in run
    self._target(*self._args, **self._kwargs)
  File "C:\Users\vs\Desktop\Арсений execute\allsave.py", line 209, in group_parsing
    VALUES (%s,%s,%s,%s)''', a[i])
psycopg2.ProgrammingError: ERROR: syntax error in tsvector: "Reggae.FM:"
LINE 3:         VALUES (181649,'Reggae.FM:'

When I added this data to the "text" field type, there were no problems. But apparently "tsvector" does not accept strings containing a colon ":" and, probably, some other characters.

The question is, how do I implement full-text search if the "tsvector" cannot store such characters?

P.S. Using "text" or "char" is not a solution; searching for such data types is very slow. I get the lines by parsing groups vk.com (Russian social network), that is, the names of all existing groups. I need to keep these names in full form that the user could find them on my site. But any solutions will help me.

Upvotes: 2

Views: 1324

Answers (1)

unutbu
unutbu

Reputation: 880777

Use to_tsvector to normalize the string and return a tsvector:

INSERT INTO ...
VALUES (%s,to_tsvector(%s),%s,%s)''', a[i])

Note that casting as tsvector would not work here:

unutbu=# select 'Reggae.FM:'::tsvector;
ERROR:  syntax error in tsvector: "Reggae.FM:"
LINE 1: select 'Reggae.FM:'::tsvector
               ^

This is what to_tsvector returns:

unutbu=# select to_tsvector('Reggae.FM:');
+---------------+
|  to_tsvector  |
+---------------+
| 'reggae.fm':1 |
+---------------+
(1 row)

Upvotes: 2

Related Questions