CopyIn behaves differently than Insert

[bhcleek]

Given an implementation a Postres enum and a Go type for that enum that implements "database/sql/driver".Valuer by returning a[]byte, CopyIn will behave differently than an INSERT statement. The CopyIn will pass a hex string to Postgres, while INSERT passes the byte slice without converting it to a hex string. For an enum, the CopyIn results in an error.

There's a workaround, though: the "database/sql/driver".Valuer implementation can return a string instead of a byte slice.

[freeformz]

This is because lib/pq supports only the default text format for COPY (at least AFAICT). Does this have any actual side effect once the data is stored though?

[bhcleek]

Once the data is stored, there's no difference. I was surprised, though, when the COPY failed while INSERT did not. Perhaps this at least warrants a mention in the documentation?

[martinrode]

Sending the text []byte encoded result in different database content for me.

With INSERT:

{"de-DE":"Nachbearbeitung von Datensätzen","en-US":"Postprocessing of data records"} 

With COPY IN:

{"de-DE":"Nachbearbeitung von Datens\303\244tzen","en-US":"Postprocessing of data records"} 

Reading the latter back using the SQL driver results in a Scan error at "3".

Without further looking into that problem, changing encodeBytea in the PG driver to simply return the []byte, fixes the problem for me.

Also, enabling the block using the \x notation also fixes the problem.

I will not put further investigation in this as the INSERT INTO with multiple VALUES currently works faster for me than COPY IN (10 columns, 30K records).

[zuoRambo]

vote for this issue!