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
>
>