0

I have table t1 and I would like to add a new column app_id with the properties given below

  • auto increment
  • increment factor(increment by 4)
  • min-value/initial value(100)
  • max-value(2000)

I have tried

alter table t4 
add column app_id int not null auto_increment = 100 ;

but which is not worked.is it possible to add such a column with the properties mentioned above?

  • what happens when max-value is reached? – McNets Jan 16 '17 at 11:10
  • @McNets when max value is reached, then which is need to be stop there. – Dream Hunter - hashADH Jan 16 '17 at 11:15
2

Here, you can use sequence to increase app_id value. Please noted if the sequence reach to its maxvalue, you will face this error ERROR: nextval: reached maximum value of sequence .

For example

create table t1 (a int, b int);

create sequence seq_t1
increment by 4
minvalue 100
maxvalue 2000;

alter table t1 add column app_id int default nextval('seq_t1');
insert into t1(a) select generate_series(1, 100) ;

  a  | b | app_id
-----+---+--------
   1 |   |    100
   2 |   |    104
   3 |   |    108
   4 |   |    112
   5 |   |    116
   6 |   |    120
   7 |   |    124
1

You can use a POSTGRES SEQUENCE.

CREATE IF NOT EXISTS SEQUENCE MySerial
    INCREMENT 4
    START WITH 100
    MAXVALUE 2000
    NO CYCLE
    OWNED BY TABLE.COLUMN_NAME;

Then you can add a default value of nextval('MySerial') in your field definition, or you can use:

INSERT INTO YourTable VALUES (nextval('MySerial'), ...);

You can get last value used with:

select curval('MySerial');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.