Thread: Types info on binary copy

Hello,

The PGresult structure returned on COPY ... FROM provides a handful of
information (https://www.postgresql.org/docs/13/libpq-copy.html):
number of fields expected and whether they are binary or not.

However it doesn't provide the types expected. For binary copy the
exact type is important: playing with it, it seems that no cast
whatsoever is applied and the errors reported are relatively low
level. For instance, passing an int4 value to an int8 field causes a
"protocol_violation: insufficient data left in message"; the other way
around is greeted with "invalid_binary_representation: incorrect
binary data format".

Naively, it would seem that once a "COPY ... FROM STDIN" is executed
successfully, the server has a pretty good idea of what data types it
is expecting. I'm wondering: is it absurd to ask for this info to be
returned as RowDescription and to be exposed by the libpq PQftype()?

Cheers

-- Daniele

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> Naively, it would seem that once a "COPY ... FROM STDIN" is executed
> successfully, the server has a pretty good idea of what data types it
> is expecting. I'm wondering: is it absurd to ask for this info to be
> returned as RowDescription and to be exposed by the libpq PQftype()?

It'd be a protocol break, unfortunately, since no such message is
sent as part of the COPY protocol.  The 'G' (begin copy in) message
only carries the number of columns and column format codes.  I agree
that for an intelligent source agent it might be useful to know the
column datatypes, but that idea didn't occur to us at the time :-(.

Now having said that, I believe we do have the ability now for a
client to request protocol extension(s) at connection start.  So
maybe you could make it work that way.  Probably a RowDescription
message before "begin copy out" would be useful too.

            regards, tom lane