Re: bytea vs. pg_dump

On Tuesday 05 May 2009 17:38:33 Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> > Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> >> Another approach would be to just dump bytea columns in binary
> >> format only (not sure how doable that is, though).
> >
> > If that's not doable, perhaps a base64 option for bytea COPY?
>
> I'm thinking plain old pairs-of-hex-digits might be the best
> tradeoff if conversion speed is the criterion. The main problem
> in any case would be to decide how to control the format option.

The output format can be controlled by a GUC parameter. And while we are at
it, we can also make bytea understand the new output format on input, so we
can offer an end-to-end alternative to the amazingly confusing current bytea
format and also make byteain() equally faster at the same time.

For distinguishing various input formats, we could use the backslash to escape
the format specification without breaking backward compatibilty, e.g.,

'\hexd41d8cd98f00b204e9800998ecf8427e'

With a bit of extra work we can wrap this up to be a more or less SQL-
conforming blob type, which would also make a lot of people very happy.