Convert Pandas dataframe to format suitable for PostgreSQL COPY ... FROM STDIN

Given a Pandas dataframe, how can it be converted to a form suitable for PostgreSQL's COPY FROM STDIN (ideally in TEXT mode)?

A naive way would be:

text = df.to_csv(
    index=False,
    header=False,
    sep='\t',
    na_rep=r'\N',
)

but this won't work in several cases. For example, when there is a tab in the data itself - it wraps such fields in quotes instead of putting a backslash in front of it, which is what's required according to https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.9.2

Note the data can have NAs, which should be converted to NULLs, and it can have tabs, quotes, newlines etc, which should be preserved.

Copyright Notice：Content Author:「Michal Charemza」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/73048026/convert-pandas-dataframe-to-format-suitable-for-postgresql-copy-from-stdin