My solution is to create a sequence with the table, before batch loading my data I will get the next value of the sequence, then reset it to that value + the size of my batch and iterate over my batch adding that value. 

I believe I may have found a bug in the sequence though... I want to make sure that this value will not have problems when working with large integers:

sql> CREATE SEQUENCE "test_seq" as bigint;
sql> select next value for test_seq;
+------------------------------+
| next_value_for_testdb |
+==============================+
|                            1 |
+------------------------------+

sql> alter sequence test_seq restart with 3000000000;
sql> select next value for test_seq;
+------------------------------+
| next_value_for_datawarehouse |
+==============================+
|                   2147483647 |
+------------------------------+

I can always increment the sequence higher, but if I try to alter it to anything past the maximum value for a 32-bit integer then it will reset to it.

What do you think?  Bug?

73,
Matthew W. Jones (KI4ZIB)
http://matburt.net


On Fri, Aug 28, 2009 at 5:55 AM, Fabian Groffen <Fabian.Groffen@cwi.nl> wrote:
On 27-08-2009 19:49:37 -0400, Matthew Jones wrote:
> On Thu, Aug 27, 2009 at 7:11 PM, Martin Kersten <Martin.Kersten@cwi.nl>
> wrote:
>
>     Matthew Jones wrote:
>     > I noticed that COPY...INTO for bulk loading requires that all
>     fields be
>     > present in the data file.  Is there any way to use bulk loading
>     and have
>     > it honor the auto increment column or at least not have to
>     specify every
>     > column in the data file?
>     How about adding the serial column after loading. It will be zero
>     cost.

Adding a "serial" column won't be zero cost, and I doubt it will be
possible, since it implies a NOT NULL constraint, which cannot be added
to a to be added column.  Technically you could do it, assuming that you
would just start filling the column because you know what can be put
into it, as is defined by the sequence.

> The problem is, I am loading continously, and I need this field
> continuously.

So it seems you're using COPY INTO to regularly append batches of new
data to your dataset.  You do this because of performance reasons?
Since COPY INTO is designed for loading (restoring) a database, is it
perhaps possible to use prepared statement batching instead?  That
allows you to have the sequence column(s) do their job.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users