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
