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