CODEWITHSUNDEEP
postgresqljdbc
Nait
Nait

Reputation: 1155

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

Upvotes: 13

Views: 31444

Answers (3)

Evgeny Nozdrev
Evgeny Nozdrev

Reputation: 1566

Max size of bytea

According to this old thread, maximum size for a field in Postgres is 1 GB.

The PostgreSQL version 12 protocol limits row size to 2 GiB minus message header when it is sent to the client (SELECTed). (The protocol uses 32-bit signed integers to denote message size.) No other limits found (another topic).

But largeobjects are stored as multiple bytea records so they not limited on such way. See this docs for them.

Upvotes: 13

Scott Marlowe
Scott Marlowe

Reputation: 8870

If you need to ensure that you don't upload the same image twice, you can create a unique index on the md5 (or some other hash) of the bytea:

create table a(a bytea);
create unique index a_bytea_unique_hash on a (md5(a));
insert into a values ('abc');
INSERT 0 1
insert into a values ('abc');
ERROR:  duplicate key value violates unique constraint "a_bytea_unique_hash"
DETAIL:  Key (md5(a))=(900150983cd24fb0d6963f7d28e17f72) already exists.

Upvotes: 9

user330315
user330315

Reputation:

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?

Upvotes: 13

Related Questions