Re: [GENERAL] Getting "insufficient data left in message" on copy with binary
Gordon Shannon writes: > Got it. The problem was a combination of 2 mis-matched data types. > ... > So, my fault, and the fix is obvious. But it does seem like a less than > ideal error message. The binary format is sufficiently non-redundant that it's hard for the code to know just what is wrong. In this case the first data column was slurping up bits that belonged to the second column, leaving not enough data for the second column when it came time to read that. > 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? I added this text: *** *** 300,305 --- 300,309 somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and PostgreSQL versions. + Also, the binary format is very data type specific; for example + it will not work to output binary data from a smallint column + and read it into an integer column, even though that would work + fine in text format. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting "insufficient data left in message" on copy with binary
Tom Lane-2 wrote: > > Gordon Shannon 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting "insufficient data left in message" on copy with binary
Gordon Shannon writes: > I'm running 8.4 on Linux/Centos. I am doing a "copy (select ) to > '/absolute/path/to/file.dat' with binary". That works fine. But when I load > that file into a table... > copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id, > content_type_id, provider_id, > is_duplicate, is_spam, language_code, profanity, tonality, sentiment, > created_time, updated_at) > from '/absolute/path/to/file.dat' with binary; > 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? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting "insufficient data left in message" on copy with binary
Hello, I'm running 8.4 on Linux/Centos. I am doing a "copy (select ) to '/absolute/path/to/file.dat' with binary". That works fine. But when I load that file into a table... copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id, content_type_id, provider_id, is_duplicate, is_spam, language_code, profanity, tonality, sentiment, created_time, updated_at) from '/absolute/path/to/file.dat' with binary; ERROR: insufficient data left in message CONTEXT: COPY mytable, line 1, column provider_id The same data works fine without the "with binary". Also, the column it's complaining about, provider_id, is a NOT NULL column, and the data is definitely there -- i.e. not a NULL in data file. I have searched for this message and mostly I see issues related to JDBC drivers, so that doesn't appear relevant. And they all talk about nul bytes (0x00), but again, how can that be relevant when I'm in binary mode? Seems like it should understand null bytes here, if that's what this is about. Anybody seen this? Thanks, Gordon -- View this message in context: http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25282935.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general