PostgreSQL – How to Add a New Column with Auto Increment, Increment Factor, Min-Value, and Max-Value

auto-incrementpostgresql

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?

Best Answer

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

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

PostgreSQL – How to Retrieve Max Primary Key Column to Insert a New Row

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

PostgreSQL – How to Retrieve Max Primary Key Column to Insert a New Row

Related Question