In section 8.16.6. Composite Type Input and Output Syntax [1]
we have this <note> text:
Remember that what you write in an SQL command will first be
interpreted as a string literal, and then as a composite. This
doubles the number of backslashes you need (assuming escape string
syntax is used). For example, to insert a text field containing a
double quote and a backslash in a composite value, you'd need to
write:
INSERT ... VALUES ('("\"\\")');
The string-literal processor removes one level of backslashes, so
that what arrives at the composite-value parser looks like
("\"\\"). In turn, the string fed to the text data type's input
routine becomes "\. (If we were working with a data type whose
input routine also treated backslashes specially, bytea for
example, we might need as many as eight backslashes in the command
to get one backslash into the stored composite field.) Dollar
quoting (see Section 4.1.2.4) can be used to avoid the need to
double backslashes.
This is a mess. I think that the example INSERT was originally
written assuming that standard_conforming_strings is OFF, so that
it had twice as many backslashes as now. Then somebody removed those
extra backslashes without considering whether the example as a whole
still made any sense at all; and it doesn't. "The string-literal
processor removes one level of backslashes" is just false given this
example. The cross-reference to dollar quoting is pretty unhelpful
too, because all that does for you is remove the need for doubling
backslashes an extra time, which is already gone with
standard-conforming strings.
My guess is that nearly nobody uses escape string syntax anymore,
so I think that rather than trying to rewrite this <note> into
something less confused, we should just nuke it altogether.
There is no corresponding text in the preceding section about
array I/O syntax, although that's just about as gnarly.
regards, tom lane
[1] https://www.postgresql.org/docs/devel/rowtypes.html#ROWTYPES-IO-SYNTAX
