Re: Bytea, ASCII-only encoding and pg_dumpall (Was: Bytea vs. BLOB)

Frank Joerdens wrote:
> concatenated). What I don't understand yet is if that also applies if
> you don't use ASCII-only encoding (how is data representet internally
> without it?) and when you'd decide to encode or not, or if the fact
> that you can dump to text would be sufficient grounds to decide to
> encode everything bytea to ASCII-only.
>

Actually, since bytea is truly treating the data as a string of bytes,
there is no notion of encoding at all. An input octet, say '\000' is
turned into exactly one byte with a value of 0. In fact, the major
diffenence between the "normal" (text, varchar, etc) functions and the
bytea ones is that the bytea ones have the multi-byte encoding specific
code removed.

A small experiment shows what the dump output would look like:

test=# CREATE TABLE foo2 (f1 bytea);
CREATE
test=# insert into foo2 values('\\003\\002\\001\\000abcdefg\\377');
INSERT 16594 1
test=# select f1 from foo2;
f1
-----------------------------
\003\002\001\000abcdefg\377
(1 row)

test=# select length(f1) from foo2;
length
--------
12
(1 row)

test=# \q
[postgres(at)jec-linux postgres]$ pg_dump -t foo2 test
--
-- Selected TOC Entries:
--
\connect - postgres

--
-- TOC Entry ID 2 (OID 16589)
--
-- Name: foo2 Type: TABLE Owner: postgres
--

CREATE TABLE "foo2" (
"f1" bytea
);

--
-- Data for TOC Entry ID 3 (OID 16589)
--
-- Name: foo2 Type: TABLE DATA Owner: postgres
--

COPY "foo2" FROM stdin;
\\003\\002\\001\\000abcdefg\\377
\.

So it seems you can avoid a pg_dump -b by using bytea.

Regards,
Joe