Incredibly, I was just sitting down to do something similar for a
problem I have when I read this email. I'm going to do a temp table
too, but I did think of another solution. It would work for me but it's
a little complex for my stage of PG expertise: Create a user-defined
type for "pic" and define an input/output function for it. Someone can
correct me but my understanding says that probably won't be as fast as a
direct bulk copy, but it can't be slower than the temp table method and
it is certainly simpler.
Jerry LeVan wrote:
Hi,
I have been looking for a fairly simple way to upload data into
a bytea field without having to write custom C code for each table
that contains a bytea field.
With some good advice from Daniel Verite and reading the fine manual
here is my procedure for uploading files to bytea fields.
1) Create an "upload table" that might look like
\d pics Table "public.pics"
Column | Type | Modifiers
--------+---------
+---------------------------------------------------------
info | text |
image | bytea |
ident | integer | not null default
nextval('public.pics_ident_seq'::text)
2) I wrote a single C procedure that would upload to this table
here is a fragment of the code:
int usage()
{
fprintf(stderr,"loadBytea <connect string> <table> <comment>
<file>\n");
fprintf(stderr," This will insert the comment (a string) and the
contents of file\n");
fprintf(stderr," into the first two columns of the specified
table.\n");
exit(0);
}
The core of the program is a wrapper around the PQexecParams routine.
The source code for the program is located here:
http://homepage.mac.levanj/Cocoa/programs/loadBytea.c
3) Once the file is uploaded (say to table pics) I can use the update
command to load the bytea field into the desired table, perhaps
something like:
update person set picture = pics.image
from pics
where pics.ident=15 and person.first_name='Marijo'
Once the bytea field has been loaded into the target, it
can be deleted from the upload table ( or a reference could
be placed in the "person" table to the appropriate picture and
all of the pictures keep in the upload table).
This method does not scale well to bulk input but I think it
would not be difficult to rewrite the loadBytea.c program as
needed.
Jerry
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
