NpgsqlBinaryImporter fails with incorrect binary data format

See original GitHub issue

I’m trying to do a binary copy from Windows to postgres on ubuntu. After writing several rows of data, the command is closed and an exception is thrown. This is the relevant Npgsql stack trace:

Npgsql.NpgsqlException (0x80004005): 22P03: incorrect binary data format
   at Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
   at Npgsql.NpgsqlConnector.ReadExpecting[T]()
   at Npgsql.NpgsqlBinaryImporter.Close()

Issue Analytics

State:
Created 8 years ago
Reactions:1
Comments:8 (3 by maintainers)

Top GitHub Comments

2reactions

mkedocommented, Mar 12, 2016

Postgresql docs says

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.

That is likely your case. Your postgresql log should give you more information on which column is the culprit.

Had this issue today. My problem was that I passed a System.DateTime value to the NpgsqlBinaryImporter.Write() method for a date column, which then was sent to postgres as a 8 byte value, but postgres requires 4 byte values for date columns. It worked after I explicitly passed NpgsqlDbType as the second argument binImporter.Write(value, NpgsqlDbType.Date);

0reactions

rojicommented, Mar 21, 2016

@mkedo thanks for the example, I wasn’t aware that PostgreSQL implicitly converts byte array input into geometry in this way.