Re: COPY FROM - how to identify results?

Jaime Silvela wrote:
> I've written a web application where users can upload spreadsheets,
> instead of having to key in forms. The spreadsheets get parsed and
> INSERTED into a table, and with the INSERT gets added an identifier so
> that I can always trace back what a particular row in the table
> corresponds to.
> I'd like to use COPY - FROM to achieve the same thing, but a stopping
> point is that I don't see how to add the new spreadsheet with a
> particular identifier.
>
> I'd like to be able to do something like
> COPY mytable (field-1, .. field-n, id = my_id) FROM file; or
> COPY mytable FROM file WITH id = my_id;
>
> A very messy solution would be to create a temp table with a special
> name, COPY to it, then INSERT from it to the permanent table. However, I
> don't want a solution of that type.
>

I may have completely misunderstood you, but i'd think that copying the
data directly from an uploaded file would be more than a little
insecure. But then, you also mentioned that you parse the uploaded file.
I don't understand how these two statements can be compatible.

Do you mean that you'd like to load the data into a table, then retrieve
the sequence ID? Presumably, if your application is really parsing the
data first, one could simply do an INSERT and then grab the last
inserted ID. Look at nextval() & currval().

http://www.postgresql.org/docs/7.3/static/functions-sequence.html

brian