LIKE search in bytea datatype (PostgreSQL)

Question

Table:

\d blob
 Column | Type | Collation | Default 
---------+--------------+-----------+------------------------------------------
 id | integer | | nextval('modlink_blob_id_seq'::regclass)
 content | bytea | | 

This statement:

SELECT COUNT(*) "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id")
 WHERE UPPER("blob"."content"::text) LIKE UPPER('%csv%');

Error message:

ERROR: invalid memory alloc request size 1989028364

What can I do to make this statement not fail?

• user1822: Why are you storing text data in a bytea column? That makes no sense. A text column would be a lot easier to deal with
• guettli: In my case the bytea columns contain plain http requests. They are mostly text, but sometimes they contain binary data. AFAIK a text column can't be used for this, but may there is a different and better solution. If you know one, please let me know.

Answer 1

Maybe you can use position function https://www.postgresql.org/docs/13/functions-binarystring.html

WHERE position('csv'::bytea in "blob"."content") > 0

Answer 2

The best way i found to make 'LIKE' search in PostgreSQL in columns with a lot of content was using tsvector columns.

One advantages is more fast than make 'like' searches and allow create index.

The boring part is create a trigger to replicate the content in a new column.

The article below shows how to use tsvector.

http://rachbelaid.com/postgres-full-text-search-is-good-enough/

Column | Type | Collation | Default 
---------+--------------+-----------+------------------------------------------
 id | integer | | nextval('modlink_blob_id_seq'::regclass)
 content | bytea | |
 search | tsvector | | 

This is the SQL

SELECT COUNT(*) FROM "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id") 
WHERE blob.search @@ plainto_tsquery('csv');

A observation: I use text columns instead bytea.

• Tobi: you could setup search as a generate stored column, right?

Answer 3

ENCODE(content, 'escape')

converts the bytea to text so you can then just continue with

WHERE UPPER(ENCODE(content, 'escape')) LIKE UPPER('%csv%')