Hello, How can I create FTS index for OID data types

Question

PostgreSQL (English)

Jaisankar

Hello, How can I create FTS index for OID data types

in postgres?

#programming #russian

0

13.10.2024

5 ответов

13 просмотров

Jaisankar Автор вопроса

Stefanie Janine Stölting
What is the goal of a full text search on an OID w...

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.

0

2 месяца назад

Stefanie Janine Stölting

Jaisankar
I'm on migration from Oracle to postgres, in oracl...

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.

0

2 месяца назад

Jaisankar Автор вопроса

Stefanie Janine Stölting
The equivalent data type of an Oracle blob is byte...

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?

0

2 месяца назад

Stefanie Janine Stölting

Jaisankar
In the ora2pg migration, the equivalent data type ...

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?

0

2 месяца назад

Stefanie Janine Stölting · Accepted Answer

Stefanie Janine Stölting

What is the goal of a full text search on an OID which is simply a number?

0

2 месяца назад

169 похожих чатов

Hello, How can I create FTS index for OID data types

5 ответов

Похожие вопросы