Fabian, George I looked at the problem and they were related. The problem was that on bulk inserts the 'next value for' was executed only once instead of once per inserted row. Both problems are now fixed (see CVS head). Niels On Tue, Sep 26, 2006 at 09:04:37AM +0200, Fabian Groffen wrote:
On 25-09-2006 16:09:08 -0700, George H wrote:
I don't think I can use the idea of creating a table with a sequence column and later doctoring that column with an ALTER SEQUENCE statement because I use a COPY INTO command to populate the table with a text file. The table must have the same number of columns as the test file for the COPY INTO statement to work and the text file has no sequence column. Do you know of some other way to populate a table from a text file that might get around this?
Not without modifying the input document.
I tried to use an INSERT statement to populate a table that already had a sequence column, but that didn't work either. Here's what I did.
sql->create table test (name varchar(10), seq serial); sql->create table test2 (name varchar(10)); sql->insert into test2 values ('a), ('b'), ('c'); [ 3 ] sql->insert into test (name) select (name) from test2; ERROR = !ERROR: INSERT INTO: PRIMARY KEY constraint 'test.test_seq_pkey' violated
This looks like a bug to me: the serial *should* expand to something like "seq int default next value for seq_XXX". In your query the default should be triggered, but apparently it is not, _OR_ the batch statement you use is dealt with the wrong way.
What version of MonetDB do you use, on which platform?
So if I use the COPY INTO statement to populate a table from my text file, I can't use the resulting table in an INSERT/SELECT statement to populate another table that has a sequence column.
That should have worked, IMO.
Is there some course of action you can think of to get a sequenced table from a text file? With 40million+ rows it sure can't be done by hand.
I think we should try to fix this bug, if it isn't already. Thanks for pointing it out.
Again, thanks for your help and also thanks for all your postings on this list. I've found them interesting and instructive.
You're welcome :)
------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl