Re: Differences in Escaped bytea's when creating a plain pg_dump

I'm back at my problem today:

> Example:
>
>
> postgres=# set standard_conforming_strings to off;
> SET
>
>
> postgres=# set escape_string_warning to off;
> SET
>
>
> postgres=# select '\000'::bytea;
> ERROR: invalid byte sequence for encoding "UTF8": 0x00
>
>
> postgres=# select '\\000'::bytea;
> bytea
> -------
> \x00
> (1 row)

I made some test with pgadmin. Pgadmin (5.2) also reports this error
now. And it doesn't matter if standard_conforming_strings is on or off.

SET standard_conforming_strings = off;

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)',
'2021-08-31 11:53:22.442801', 0, 1);

And

SET standard_conforming_strings = on;

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)',
'2021-08-31 11:53:22.442801', 0, 1);

Both do report:

WARNUNG: nicht standardkonforme Verwendung von Escape in
Zeichenkettenkonstante
LINE 8: ...(1, 'Figure_Wolle1', 476, -476, 2000, 2400, 2400, '\000\000&...
^
HINT: Verwenden Sie die Syntax für Escape-Zeichenketten, z.B. E'\r\n'.

ERROR: FEHLER: ungültige Byte-Sequenz für Kodierung »UTF8«: 0x00

The warning can be avoided by adding the E before the string constant.

The only solution to avoid the error is, to double-backslash.

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
2000, 2400, 2400, E'\\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)',
'2021-08-31 11:53:22.442801', 0, 1);

I also dumped the table again after INSERT, and the data was correct
(but again without E and with single backslash)

What I cant understand: why does pg_dump produce the string without the
E and without double-backslash, when it is needed? Now I have to write a
correction routine in c++, what fixes the dumps, before using them.

--
May the source be with you