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