Re: random access - bytea

Dennis Bjorklund wrote:
> There have been (in the past at least) plans on adding a random access
> interface to bytea fields. I don't find it in the todo, maybe it should be
> added?

First do this:
ALTER TABLE foo ALTER COLUMN foo_bytea SET STORAGE EXTERNAL;

see:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-altertable.html

Then do:
UPDATE foo SET foo_bytea = foo_bytea || '';

That will modify the bytea column so that it is stored uncompressed in
the TOAST table.

Now, simply use substr() to grab any random chunk:
SELECT substr(foo_bytea, 20000, 100) from foo where foo_id = 42;

When TOASTed columns are stored uncompressed, substring will grab just
the needed chunks from the TOAST table directly. In contrast, when the
field is stored compressed, it grabs the entire thing, uncompresses it,
then gets the piece you asked for.

HTH,

Joe