Hi,
> Right, I want to use it with a bulk operation, say importing a million records
> with COPY. Calling nextval one million times looks to me like an enormous waste
> of resources. Suppose, you are on an ADSL line: it will cost one million times
> the ping time of the ADSL line (say 10 milliseconds per call). Well OK, one
> could write a server function that does this, but then the one million result
> values must be transported back to the client, because they are not guaranteed
> to be contiguous. Unneeded complexity compared to a simple nextval increment
> parameter.
The usual way to use nextval() is to use it on the server as an expression in
an INSERT or DEFAULT. If you're using COPY and don't have a column default set
up then, hm, I guess you're kind of stuck. That would make a good use case for
a one-time nextval(increment) or something like that.
Coincidently, I very briefly discussed (offline) about supporting expressions while doing loads using COPY FROM with Heikki a while back. From the above mail exchanges, it does appear that adding this kind of functionality will be useful while doing bulk imports into tables using COPY.
Heikki's initial suggestion was as follows:
COPY <table> FROM <file> USING <query>
Where query could be any SELECT query, executed once for row using the values from the input data file. For example:
COPY footable (strcol, strcollen, moredata) FROM <file> USING SELECT $1, length($1), $2;
The sql expressions could refer to the columns being read or could be user defined procedures, built-in functions etc too. These expressions would need to be executed per row read from the input data file to form a new set of values[], nulls[] entries before forming the corresponding tuple entry.
I think the above will be a very useful enhancement to COPY. The syntax and other details mentioned above are ofcourse subject to discussion and approval on the list.
Regards,
Nikhils
--
EnterpriseDB
http://www.enterprisedb.com
