Adding URN or ID column

Database

Adding URN or ID column

Hi - I want to add a sequential column to a table so I have a urn/id field.

I have tried csum(1,0)... or similar variations but TD just runs out of spool space when I run it on my main table of 1.5 million records.

Any ideas?

Thanks

Andrew
3 REPLIES

Re: Adding URN or ID column

Hi Andrew,
you can try to create a new_table with identity column (sk_id INTEGER GENERATED ALWAYS AS IDENTITY) and insert into this new empty table INSERT INTO new_table (list of all columns except sk_id) SELECT list of all columns FROM old_table;
DROP old_table
RENAME TABLE X TO old_table

brgds

Petr
N/A

Re: Adding URN or ID column

Hi Andrew,
"csum(1,0)... or similar variations" probably means that you used x as ordering.
This is not column x, but the value x and results in *all* rows to be sent to a single AMP, thus "no more spool space".
Check the explain: "redistributed by value to all amps"

Instead of the deprecated CSUM better use:
ROW_NUMBER() OVER (ORDER BY any_colum_which_is_not_too_non_unique)

If you need a "random" order you might use
ROW_NUMBER() OVER (ORDER BY HASHROW(col1,col2,col3,col4,...))

Dieter

Re: Adding URN or ID column

Thanks to you both for the reply.

I will look into what you have suggested.

Regards

Andrew