sql - Postgresql, increment column based on other column values

Question

I have table like

create table test(employee integer NOT NULL, code character varying(200), number integer)

I want to auto increment column 'number' on every insert record

insert into test(employee, code) values(17,'bangalore')
insert into test(employee, code) values(17,'bangalore')
insert into test(employee, code) values(17,'mumbai')

I want result like

employee    code        number
17          bangalore   1
17          bangalore   2
17          bangalore   3
17          mumbai      1
17          mumbai      2
17          bangalore   4
17          mumbai      3
18          bangalore   1
18          bangalore   2
18          mumbai      1
18          mumbai      2

Do not post code or additional information in comments. edit your question. — user330315
– user330315, Commented Jun 2, 2017 at 5:57
Why do you want to store that information? You can easily generate those numbers while retrieving the data. — user330315
– user330315, Commented Jun 2, 2017 at 5:59
i want to store as the same data is referenced at multiple places — Onkar Patil
– Onkar Patil, Commented Jun 2, 2017 at 6:00
Your table has no primary key yet. And, with your design, it naturally would be the whole table. Why not just add a single serial to have a smaller primary key? It would make the ordering clear too. — pozs
– pozs, Commented Jun 2, 2017 at 7:34

Raghavendra Kedlaya · Accepted Answer · 2017-06-02 09:42:15Z

1

For a batch upload of data, try if below approach would be useful.

create a temporary table test2

create table test2(employee integer NOT NULL, code character varying(200))

insert into test2(employee, code) values(17,'bangalore')
insert into test2(employee, code) values(17,'bangalore')
insert into test2(employee, code) values(17,'mumbai')

Insert into actual table along with incremental number

insert into test(employee, code, number) 
select employee, code, row_number() over (partition by code )  from test2

You could include order by clause like primary key column or another column like created_date :

over (partition by code order by created_date)

answered Jun 2, 2017 at 9:42

Raghavendra Kedlaya

1163 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahad Anjum · Accepted Answer · 2017-06-02 07:47:42Z

0

create table test (employee integer NOT NULL, code character varying(200), number integer)

    insert into test(employee, code, number ) values(17,'bangalore',(select coalesce(max(number) + 1,1)  from test where employee = 17 and code = 'bangalore'));
    insert into test(employee, code, number ) values(17,'bangalore',(select coalesce(max(number) + 1,1)  from test where employee = 17 and code = 'bangalore'));
    insert into test(employee, code, number ) values(17,'mumbai',(select coalesce(max(number) + 1,1)  from test where employee = 17 and code = 'mumbai'));

edited Jun 2, 2017 at 7:47

answered Jun 2, 2017 at 7:16

Fahad Anjum

1,2561 gold badge10 silver badges19 bronze badges

5 Comments

Laurenz Albe Over a year ago

I would use max rather than count. What if some rows are deleted?

Fahad Anjum Over a year ago

@LaurenzAlbe, Yes you are right. and we have to change count(*) + 1 to coalesce(max(number) + 1,1).

Onkar Patil Over a year ago

This works fine for single insert, but i wanted for batch update.

Onkar Patil Over a year ago

This works fine for batch update but the same time if i do batch update second time then the problem comes.

Fahad Anjum Over a year ago

what is problem can you please explain.

Collectives™ on Stack Overflow

sql - Postgresql, increment column based on other column values

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related