Обсуждение: 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@postgresql.org >[mailto:pgsql-interfaces-owner@postgresql.org] On Behalf Of >Jeroen Vermeulen >Sent: Tuesday, December 09, 2008 10:53 AM >To: Whit Armstrong >Cc: pgsql-interfaces@postgresql.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@postgresql.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@xs4all.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@xs4all.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@xs4all.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.