CODEWITHSUNDEEP
postgresqlsequence
jsstuball
jsstuball

Reputation: 4951

SERIAL for each value in another column

I want to have one column be an auto-incrementing sequence which counts up within values of other column(s).

e.g.

|  flav  | count|
| 'choc' |   1  |
| 'choc' |   2  |
|'banana'|   1  |
| 'choc' |   3  |
|'banana'|   2  |
| 'mint' |   1  |
|'banana'|   3  |
|   ...  |  ... |

An individual row insert could of course be achieved with 2 separate queries, a SELECT COUNT followed by an INSERT, but this doesn't allow efficient batch inserts and I'm sure there's a better way.

I want to be able to just do INSERT INTO orders (flav) VALUES ('choc'); and have the schema handle the count column, like it would handle an auto-incrementing column.

I tried to define a DEFAULT expression for the count column in the schema which would be a SELECT COUNT query, but no dice: ERROR: cannot use subquery in DEFAULT expression.

Upvotes: 0

Views: 127

Answers (1)

Belayer
Belayer

Reputation: 14934

Use the window function row_number.

with flavor_list (flavor) as
 ( values 
           ( 'choc' )
         , ( 'choc' )
         , ('banana')
         , ( 'choc' )
         , ('banana')
         , ( 'mint' )
         , ('banana')
  ) 
select flavor, row_number() over(partition by flavor) 
 from flavor_list;

Upvotes: 1

Related Questions