2

I am trying to add an identity column in a table through alter query using Ingres DB. While creating the table, i am able to define the identity column but not when i am trying to add it through alter query. Kindly Suggest me an alter query for it.

Sriparna Banerjee
  • 21
  • 1

1 Answers1

2

It's not as straightforward as you might think, "alter table" has a a number of restrictions which make this a multi-step operation. Try this:

create table something(a integer, b varchar(20)) with page_size=8192;
alter table something add column c integer not null with default;
modify something to reconstruct;
alter table something alter column c integer not null generated always as identity;
modify something to reconstruct;
G Jones
  • 343
  • 1
  • 6
  • Amazing..it is working...Thank you!!!! Another problem.. M trying to Insert 100,000 records at 1 go..but it is not working.. Only 461 records are getting inserted. How to fix it – Sriparna Banerjee Mar 19 '18 at 07:49
  • but it is getting the value as 0. The entire column has the value 0..It is not becoming an identity – Sriparna Banerjee Mar 21 '18 at 09:14
  • Could be a bug that's been fixed in a later version or patch level, all looks ok here (using Ingres 10.2.0 +p15291). – G Jones Mar 21 '18 at 12:11