There are two things going on here. Pg uses the text I/O functions for datatypes by default, and even if you explicitly request
bytea, the default protocol still exchanges
bytea as text using the
PostgreSQL clients use the text-mode protocol by default when talking to PostgreSQL. When you
geometry column, PostgreSQL uses the text mode output function for the type, which is
geometry_out. In PostGIS, this formats the data as hex for output, because the text protocol cannot transmit arbitrary binary so it can't send the raw on-disk format directly.
If you use functions like
ST_AsBinary that return
bytea, you still get hex output when using the default protocol. It's formatted like a PostgreSQL
bytea literal as it's converted via the
bytea_output setting into text for sending to the client, but it's still textual. That's because the PostgreSQL text protocol cannot carry arbitrary binary data without it being encoded, so the server converts it after the PostGIS function returns binary. Many client drivers automatically convert it back to binary on the client side, so you may not notice or care about this text translation, but it can be a performance issue for very high volume applications - which is why PostgreSQL supports a binary protocol mode.
If you enable the PostgreSQL binary protocol in your client, two things happen:
geometry_send instead of
geometry_out when you
geometry column. This sends the geometry as
bytea fields directly as bytes + length to the client in the binary protocol instead of converting them via
So what you need to do is enable the PostgreSQL binary protocol in your client. With
libpq you'd use
I couldn't find any evidence that nPgSQL supports the binary protocol at all, unfortunately. It's a completely independent driver, not based on
libpq, so it doesn't support all server and protocol features.
If you put the client in binary protocol mode you don't need to use
geometry_send will deliver binary anyway.
If you aren't using the binary protocol there's no way to transfer un-encoded binary, because the text mode protocol just doesn't support it.
Under all circumstances the
geometry data is still stored in a compact binary form on disk.