-1

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?

  • 1
    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 – a_horse_with_no_name Oct 15 '18 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. – guettli Oct 15 '18 at 19:11
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.