Postgresql and BLOBs - maximum size of bytea?

postgres bytea
postgres bytea example
postgresql blob
postgres bytea vs blob
postgresql blob example java
postgres data types
postgresql bytea fixed size
postgres bytea length

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

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?

PostgreSQL, EDB Postgres and PostgreSQL use a fixed page size (commonly 8 KB), most TOASTable column types are limited to a max size of approximately 1GB. where BLOBs are equivalent to the BYTEA data type and CLOBs are� PostgreSQL offers the type BYTEA for storing raw data. A BYTEA column can theoretically hold values of any length, but it appears that the maximum length is 1GB. The size of a BYTEA value is 4 bytes plus the actual number of bytes in the value. Syntax for Literal Values. Entering a BYTEA value can be a little tricky.

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.

BinaryFilesInDB, PostgreSQL offers the type BYTEA for storing raw data. A BYTEA column can theoretically hold values of any length, but it appears that the maximum length is � bytea size limit?. I everyone, need help!!! My aplication return erro: 2009-01-20 10:22:03,264 INFO [STDOUT] Caused by: org.postgresql.util. PSQLException: ERROR: invalid memory alloc request size

Max size of bytea

According to this thread, maximum size for a field in Postgres is 1 GB. 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.

Documentation: 9.0: Binary Data Types, currently 1GB. Ken -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http� EDB Postgres and PostgreSQL use a fixed page size (commonly 8 KB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly.

PostgreSQL Toast and Working with BLOBs/CLOBs Explained, Is there a size limit setting somewhere I need to set up? The DB table has two fields: id: integer data: bytea import java.sql. to load a blob of 30Mb+ (with a heap size of 32Mb) against 1.5Mb yesterday with the same heap. The octet_length function returns the length in bytes size of a bytea field. From the docs: “Number of bytes in binary string”.

BLOBs :: Chapter 2. Working with Data in PostgreSQL :: Part I , In PostgreSQL there are two ways for storing Binary Large OBjects (BLOBs). BYTEA type or binary string is very similar to simple character strings, like varchar and text. First, binary strings specifically allow storing octets of value zero and other BYTEA. OID. Max. allowed space. 1 GB. 2 GB. Data access. As a whole. > There are 608 blobs of size 460800 bytes. All blobs are updated piecewise > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)

general - bytea size limit?, Recent releases of PostgreSQL have seen most database limits relaxed, and in many cases, effectively removed. bytes of data. Its use is discouraged, The image table will now have a non-NULL oid that references the BLOB: bpfinal=#� Yes, if you store the BLOBs as bytea. The limits will be per partition. If you want to use LOBs, there cannot be more than 2^32 per database.

Comments
  • Please show us the code and the exact error message (ideally including the stacktrace). There is no problem with storing (a lot) more than 8KB in a bytea column
  • Who throws the error? I guess the error comes from the web server where the 8k limit is common for uploads.
  • I don't think I want to answer that question :(
  • binary data isn't necessarily large. It is very reasonable to use a bytea column with an index if you only store a few bytes in the bytea.