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

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.