Re: Getting "insufficient data left in message" on copy with binary

Tom Lane-2 wrote:
>
> Gordon Shannon <gordo169@gmail.com> writes:
>
>>   ERROR:  insufficient data left in message
>>   CONTEXT:  COPY mytable, line 1, column provider_id
>
>> Anybody seen this?
>
> No.  Can you extract a self-contained test case?
>


Got it.  The problem was a combination of 2 mis-matched data types.
Consider this test case:

----------------begin-------------------

drop table if exists bar;
drop table if exists foo;

create table foo (
system_id smallint,
credibility real not null
);

insert into foo ( system_id, credibility) values (1,1);

copy foo to '/tmp/repeat.dat' with binary;

create table bar (
system_id int,
credibility numeric(10,9) not null
);

copy bar from '/tmp/repeat.dat' with binary;

copy bar from '/var/lib/pgsql/backups/repeat.dat' with binary;
psql:repeat:19: ERROR:  insufficient data left in message
CONTEXT:  COPY bar, line 1, column system_id

--------------end-----------------

It's interesting to note that I get this error only when there are 2 bad
fields.
If I fix only the numeric field, I get "incorrect binary data format" on the
int field.
If I fix only the smallint field, I get "invalid length in external
"numeric" value on the real field.

So, my fault, and the fix is obvious.  But it does seem like a less than
ideal error message.

Also, maybe a sentence like this would be helpful on the COPY page: "Be
careful that the data types match from 'copy to' to 'copy from'.  There is
no implicit conversion done in binary mode..." or some such?

Cheers

--
View this message in context:
http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25287583.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.