4

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?

Improve this question
2
  • 2
    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 Commented Oct 15, 2018 at 14:30
  • 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. Commented Oct 15, 2018 at 19:11

3 Answers 3

Reset to default
4

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

WHERE position('csv'::bytea in "blob"."content") > 0
Improve this 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.

Improve this answer
1
  • you could setup search as a generate stored column, right? Commented Mar 27, 2021 at 14:27
0 
ENCODE(content, 'escape')

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.