in postgres?
What is the goal of a full text search on an OID which is simply a number?
I'm on migration from Oracle to postgres, in oracle side the data types is blob ,it converts as OID in postgres. While creating FTS index it's suggest that the data types should be converted as text.
The equivalent data type of an Oracle blob is bytea. It's not known, what the content of a binary object is. And OIDs are only used internal in PostgreSQL and they represent a number. You should first extract the blob objects into text, preferable LONG. That can be inserted into PostgreSQL TEXT. And if you are needing text searches there, and only than, create a full text index on that column. TEXT can store up to 2 GB, that's a lot of content.
In the ora2pg migration, the equivalent data type for BLOB in PostgreSQL is BYTEA. However, when handling BLOB larger than 1GB, PostgreSQL encounters out-of-memory issues. To address this, we decided to use the lo_import method in ora2pg, which changes the data type to OID. The BLOB data is then uploaded and stored separately, with the table only referencing the object ID. However, Full Text Search (FTS) indexes cannot be directly created on OID data types. How can this issue be resolved?
You cannot search for text on numbers, which is an OID, therefore no FTS. You cannot search for text on hex data which binary objects are, therefore no FTS. If your binary data is representing text, make it text to be able to use FTS. What's that hard to understand the difference between text and other data types?
Обсуждают сегодня