Copy Binary VarChar Dumper

[bkief]

I noticed there is no registered register_dumper for the varchar type. Was this an oversight or is there a technical reason? Any work arounds available to do a binary insert including some varchar fields?

[dvarrazzo]

Hello,

a dumper maps a Python type to a Postgres type, so there are loaders registered on varchar, but not dumpers.

psycopg/psycopg/psycopg/types/string.py

Lines 163 to 197 in d2e6a3b

	def register_default_adapters(context: AdaptContext) -> None:
	adapters = context.adapters
	# NOTE: the order the dumpers are registered is relevant. The last one
	# registered becomes the default for each type. Usually, binary is the
	# default dumper. For text we use the text dumper as default because it
	# plays the role of unknown, and it can be cast automatically to other
	# types. However, before that, we register a dumper with the text oid,
	# which will be used when a text dumper is looked up by oid.
	adapters.register_dumper(str, StrBinaryDumper)
	adapters.register_dumper(str, StrDumper)
	adapters.register_dumper(str, StrDumperUnknown)
	adapters.register_loader(postgres.INVALID_OID, TextLoader)
	adapters.register_loader("bpchar", TextLoader)
	adapters.register_loader("name", TextLoader)
	adapters.register_loader("text", TextLoader)
	adapters.register_loader("varchar", TextLoader)
	adapters.register_loader('"char"', TextLoader)
	adapters.register_loader("bpchar", TextBinaryLoader)
	adapters.register_loader("name", TextBinaryLoader)
	adapters.register_loader("text", TextBinaryLoader)
	adapters.register_loader("varchar", TextBinaryLoader)
	adapters.register_loader('"char"', TextBinaryLoader)
	adapters.register_dumper(bytes, BytesDumper)
	adapters.register_dumper(bytearray, BytesDumper)
	adapters.register_dumper(memoryview, BytesDumper)
	adapters.register_dumper(bytes, BytesBinaryDumper)
	adapters.register_dumper(bytearray, BytesBinaryDumper)
	adapters.register_dumper(memoryview, BytesBinaryDumper)
	adapters.register_loader("bytea", ByteaLoader)
	adapters.register_loader(postgres.INVALID_OID, ByteaBinaryLoader)
	adapters.register_loader("bytea", ByteaBinaryLoader)

With a basic test, it seems to work for me:

>>> import psycopg
>>> conn = psycopg.connect(autocommit=True)
>>> cur = conn.cursor()
>>> cur.execute("create table testcopybin (id serial primary key, data varchar(20))")
>>> with cur.copy("copy testcopybin (data) from stdin (format binary)") as copy:
...     copy.write_row(("hello",))

>>> cur.execute("select * from testcopybin").fetchall()
[(1, 'hello')]

If it doesn't for you, can you provide an example?

Thank you very much!

[dvarrazzo]

...ah, maybe I understand:

>>> copy.set_types(["varchar"])

ProgrammingError: cannot find a dumper for type varchar (oid 1043) format BINARY

if this is the problem, probably you can use "text" in set_types() instead of varchar, because the two types have the same binary representation.

Is the issue coming from here? Does registering text instead work?