Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql and BLOBs - maximum size of bytea?

I'm currently trying to store images in a psql table and was following this guide here using a bytea for the image. Problem is that the image I'm trying to insert is ~24kb and I keep getting an error that the maximum size is 8191, though I've read in other places that a bytea should be able to store up to 1gb. Surely I should be able to raise this max limit somehow?

Code:

String query = "INSERT INTO " + tableName + " VALUES(?);";
try {
    PreparedStatement stmt = conn.prepareStatement(query);
    File file = new File(location);
    FileInputStream fi = new FileInputStream(file);
    stmt.setBinaryStream(1, fi, (int)file.length());
    boolean res = stmt.execute();
    stmt.close();
    fi.close
    return res;
}

The database table only consists of a bytea at the moment.

Error message:

org.postgresql.util.PSQLException: ERROR: index row requires 23888 bytes, maximum size is 8191
like image 488
Nait Avatar asked Apr 03 '12 12:04

Nait


1 Answers

Apparently you have an index on that column (to be honest I'm surprised that you could create it - I would have expected Postgres to reject that).

An index on a bytea column does not really make sense. If you remove that index, you should be fine.

The real question is: why did you create an index on a column that stores binary data?

like image 116
a_horse_with_no_name Avatar answered Sep 27 '22 23:09

a_horse_with_no_name