Re: PQgetvalue failed to return column value for non-text data in binary format

Thanks you very much for your help and clarification.  As programmer in 
heart, one tends to go straight the specific api.  I guess once one 
become so comfortable with a particular open source software, then one 
can go to the source to understand what is happening there.  But in my 
case, I guess I am far from that level of sophistication in 
understanding PostgreSQL.

This is interesting position to be taken by PostgreSQL.  For simple 
query, particularly with regards to reporting, one can go about using 
text for all column.  But a lot of time, I deal with large dataset 
ingestions and extracts that tends to take many hours to perform.  So I 
usually prefer binary form data for performance reason.  To that end, I 
found there is a significant different in the time it takes to ingest or 
extract data from one database to another database of totally different 
vendor.  Most of our company's works are done in Java nowadays, but for 
large data ingestions/extract/conversion, a C program out performs Java 
program fair and square, no debate there (based on actual run-time 
data).  For clarification, we deal with various business partners that 
we have to import/export large amount of data between heterogeneous 
databases that the wonderful technology of database replication does not 
apply for our use.

For this kind of operations, I even found the performance gain by 
creating C program out-weight a Java program for that same purpose.

I can get by using the socket function, ntohl() and ntohs() for smallint 
and int4 for the conversion.  But there is no such thing for the float4 
and float8 as well as the date, timestamp you mentioned.  I wish there 
is such function or macro available for the C interface. 

I do not agree with the "ease of portability" issue, because if that is 
the case, one can always give up performance and use Java for that 
purpose.  Beside, it should not be that much more difficult to provide 
win32 exe for PostgreSQL with PQgetvalue() that return int2, int4, 
float4, float8, date, datetime/timestamp, etc.

Jeff,

Andrew McNamara wrote:
> The data returned in binary mode is in a (largely undocumented)
> PostgreSQL form. Types like int4, and float8 are returned in network
> byte order [1], while other types such as timestamps are in more subtle
> formats. The only canonical reference to these formats is the PostgreSQL
> source. 
>
> In general, you should just use the text format, as it's more portable
> and less likely to change over time, although the specifics of the text
> format are also largely undocumented.
>
> [1] http://en.wikipedia.org/wiki/Endianness
>
>