simple example of copying data from a c/c++ array into postgres

would someone mind showing me an example of making an insert from binary
data to postgres?
I'm currently doing the inserts via a scripting languge, but I would like to
do them directly in c/c++ code.

my data looks like this (date and double):

2008-12-03 2.6585
2008-12-04 2.5513
2008-12-05 2.7037
2008-12-08 2.7378

my script does something like this:

insert into kls_clnt.obs (id, asofdate, datavalue) values
(100, 2008-12-03, 2.6585);

for every row of the data.

Since, I already have this data in a C array, it must be faster to iterate
through the rows of the array and persist the data directly from C to the
database. Can someone direct me to a simple example of doing this?

Thanks,
Whit

Whit Armstrong wrote:
> would someone mind showing me an example of making an insert from binary
> data to postgres?

Not an example, but have a look at the COPY command:

http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

COPY FROM stdin lets you insert data in bulk, without having to issue a
new INSERT for every row. There are some handy libpq functions for
feeding data into this mechanism:

http://www.postgresql.org/docs/8.3/interactive/libpq-copy.html

The "binary" part of what you're asking for is also possible, but
probably doesn't buy you all that much. Chances are you'd need to do
some conversions anyway, and it introduces all sorts of maintenance risk
for an optimization that's not likely to matter as much as disk flushes,
network transfers etc.

Jeroen

If you still want to do things as a straight insert, here's the key
lines from a test program I put together, minus all the error handling,
freeing of PGresult objects, etc... I haven't actually tried inserting
floating point values, but I'm guessing it's similar to what is below.
Best way to tell is to try it, and see what ends up in the database.

PGresult *prep_result = PQprepare(conn, "exec_stmt", sql, 0, NULL);
#ifdef intparam
// Oid paramTypes[1] = { INT4OID }; // from pg_type.h, needed if you
use PQexecParams
// you can't actually include pg_type.h easily because it has all sorts
of dependencies on other
// internal header files. I ended writing a short script that
preprocesses that file and pulls
// out just the OID defines.
int fff = atoi(argv[1]);
fff = htonl(fff);
const char *paramValues[] = { (char *)&fff };
int paramFormats[1] = { 1 }; // binary format
int paramLengths[1] = { sizeof(int) };
#else
date dfff;
dfff = PGTYPESdate_from_asc(datebuf, NULL);
// datebuf contains a YYYY-MM-DD format string
// Oid paramTypes[1] = { DATEOID };
dfff = htonl(dfff);
const char *paramValues[] = { (char *)&dfff };
int paramFormats[1] = { 1 }; // binary format
int paramLengths[1] = { sizeof(dfff) };
#endif
PGresult *exec_result = PQexecPrepared(conn, "exec_stmt", 1,
paramValues, paramLengths, paramFormats, 0);

>-----Original Message-----
>From: pgsql-interfaces-owner(at)postgresql(dot)org
>[mailto:pgsql-interfaces-owner(at)postgresql(dot)org] On Behalf Of
>Jeroen Vermeulen
>Sent: Tuesday, December 09, 2008 10:53 AM
>To: Whit Armstrong
>Cc: pgsql-interfaces(at)postgresql(dot)org
>Subject: Re: [INTERFACES] simple example of copying data from
>a c/c++ array into postgres
>
>Whit Armstrong wrote:
>> would someone mind showing me an example of making an insert
>from binary
>> data to postgres?
>
>Not an example, but have a look at the COPY command:
>
>http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
>
>COPY FROM stdin lets you insert data in bulk, without having
>to issue a
>new INSERT for every row. There are some handy libpq functions for
>feeding data into this mechanism:
>
>http://www.postgresql.org/docs/8.3/interactive/libpq-copy.html
>
>The "binary" part of what you're asking for is also possible, but
>probably doesn't buy you all that much. Chances are you'd need to do
>some conversions anyway, and it introduces all sorts of
>maintenance risk
>for an optimization that's not likely to matter as much as
>disk flushes,
>network transfers etc.
>
>
>Jeroen
>
>--
>Sent via pgsql-interfaces mailing list
>(pgsql-interfaces(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-interfaces
>

Thanks, Jeroen,

I've used copy before from the prompt. Thanks for alerting me to the
psql functions for it.

can copy be used to append data to a table?

The only reason I'm hesitant to use the copy command is because I
would have to render every row of my data to a string, and 64bit
doubles could mean long strings, formating issues, etc.

roughly what I was thinking was (this is pseudocode):

PGtuple myTuple = createTuple();
myTuple.push_back(toPGDate(myDate));
myTuple.push_back(toPGDouble(myDouble));
etc...

PGinsert_tuple(conn, myDestinationTable, myTuple);

are there facilities to do something like that, or am I really reaching here?

Thanks,
Whit

On Tue, Dec 9, 2008 at 11:53 AM, Jeroen Vermeulen <jtv(at)xs4all(dot)nl> wrote:
> Whit Armstrong wrote:
>>
>> would someone mind showing me an example of making an insert from binary
>> data to postgres?
>
> Not an example, but have a look at the COPY command:
>
> http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
>
> COPY FROM stdin lets you insert data in bulk, without having to issue a new
> INSERT for every row. There are some handy libpq functions for feeding data
> into this mechanism:
>
> http://www.postgresql.org/docs/8.3/interactive/libpq-copy.html
>
> The "binary" part of what you're asking for is also possible, but probably
> doesn't buy you all that much. Chances are you'd need to do some
> conversions anyway, and it introduces all sorts of maintenance risk for an
> optimization that's not likely to matter as much as disk flushes, network
> transfers etc.
>
>
> Jeroen
>

Whit Armstrong wrote:

> can copy be used to append data to a table?

Yes. The only thing it will not do for you is update existing rows.

> The only reason I'm hesitant to use the copy command is because I
> would have to render every row of my data to a string, and 64bit
> doubles could mean long strings, formating issues, etc.
>
> roughly what I was thinking was (this is pseudocode):
>
> PGtuple myTuple = createTuple();
> myTuple.push_back(toPGDate(myDate));
> myTuple.push_back(toPGDouble(myDouble));
> etc...
>
> PGinsert_tuple(conn, myDestinationTable, myTuple);
>
> are there facilities to do something like that, or am I really reaching here?

In libpqxx I mostly provide self-made text conversions, but you can also
use the standard library to "print" most values into text buffers. Just
be sure to use the C locale where you do this, or you might end up with
strange bugs depending on local notations!

Jeroen

Thanks, Jeroen.

just want to make sure I understand the convention for libpq and libpqxx.

the convention for getting data into postgres is to convert everything
to strings first? even if the data is already stored as a 64bit
double in the client app?

-Whit

On Thu, Dec 11, 2008 at 11:20 AM, Jeroen Vermeulen <jtv(at)xs4all(dot)nl> wrote:
> Whit Armstrong wrote:
>
>> can copy be used to append data to a table?
>
> Yes. The only thing it will not do for you is update existing rows.
>
>
>> The only reason I'm hesitant to use the copy command is because I
>> would have to render every row of my data to a string, and 64bit
>> doubles could mean long strings, formating issues, etc.
>>
>> roughly what I was thinking was (this is pseudocode):
>>
>> PGtuple myTuple = createTuple();
>> myTuple.push_back(toPGDate(myDate));
>> myTuple.push_back(toPGDouble(myDouble));
>> etc...
>>
>> PGinsert_tuple(conn, myDestinationTable, myTuple);
>>
>> are there facilities to do something like that, or am I really reaching
>> here?
>
> In libpqxx I mostly provide self-made text conversions, but you can also use
> the standard library to "print" most values into text buffers. Just be sure
> to use the C locale where you do this, or you might end up with strange bugs
> depending on local notations!
>
>
> Jeroen
>

Whit Armstrong wrote:
> Thanks, Jeroen.
>
> just want to make sure I understand the convention for libpq and libpqxx.
>
> the convention for getting data into postgres is to convert everything
> to strings first? even if the data is already stored as a 64bit
> double in the client app?

Hi Whit,

Sorry for taking so long; for some reason my mail client doesn't seem
convinced that I want to know about new mail in this mailbox.

The answer to your question is: yes, that is the default. If you want
to transfer binary data, it's still up to you to convert those doubles
from your client's native format to the right format to transfer them in.

I've never used binary mode myself so I don't know off the top of my
head what that format is--there's a good chance that all it takes is
some variant of ntohl() but the details will depend on the architecture,
OS, and possibly compiler (and/or compiler configuration) you work with.

Which by the way is one reason not to talk binary outside your
application unless you have to!

Jeroen

Thanks for you reply. I'll definitely stick to convention.

Cheers,
Whit

On Sat, Dec 20, 2008 at 11:01 AM, Jeroen Vermeulen <jtv(at)xs4all(dot)nl> wrote:
> Whit Armstrong wrote:
>>
>> Thanks, Jeroen.
>>
>> just want to make sure I understand the convention for libpq and libpqxx.
>>
>> the convention for getting data into postgres is to convert everything
>> to strings first? even if the data is already stored as a 64bit
>> double in the client app?
>
> Hi Whit,
>
> Sorry for taking so long; for some reason my mail client doesn't seem
> convinced that I want to know about new mail in this mailbox.
>
> The answer to your question is: yes, that is the default. If you want to
> transfer binary data, it's still up to you to convert those doubles from
> your client's native format to the right format to transfer them in.
>
> I've never used binary mode myself so I don't know off the top of my head
> what that format is--there's a good chance that all it takes is some variant
> of ntohl() but the details will depend on the architecture, OS, and possibly
> compiler (and/or compiler configuration) you work with.
>
> Which by the way is one reason not to talk binary outside your application
> unless you have to!
>
>
> Jeroen
>

Hi,
Could you please also give an example to insert a float number.
Thanks,

--
View this message in context: http://postgresql.nabble.com/simple-example-of-copying-data-from-a-c-c-array-into-postgres-tp2210446p5945261.html
Sent from the PostgreSQL - interfaces mailing list archive at Nabble.com.