Postgres create id for groups

Question

I am trying to solve a problem that at the beginning looked quite easy but I couldn't find any (easy) solution

I have a table with several columns and I want to create an ID over a group defined by some of the columns

better to show on example, three columns and I want to have ID on groups defined by col1 and col2, which looks like a job for a window function, but somehow I failed to do it

col1 col2 col3 ID
val1 valA x    1
val1 valA y    1
val1 valB y    2
val2 valC z    3
val3 valA v    4
val3 valA r    4

Answer 1

Actually it's pretty easy using the Dense_Rank analytic function:

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE Table1
    ("col1" varchar(4), "col2" varchar(4), "col3" varchar(1))
;

INSERT INTO Table1
    ("col1", "col2", "col3")
VALUES
    ('val1', 'valA', 'x'),
    ('val1', 'valA', 'y'),
    ('val1', 'valB', 'y'),
    ('val2', 'valC', 'z'),
    ('val3', 'valA', 'v'),
    ('val3', 'valA', 'r')
;

Query 1:

select col1, col2, col3
     , dense_rank() over (order by col1, col2) id
  from table1

Results:

| col1 | col2 | col3 | id |
|------|------|------|----|
| val1 | valA |    x |  1 |
| val1 | valA |    y |  1 |
| val1 | valB |    y |  2 |
| val2 | valC |    z |  3 |
| val3 | valA |    v |  4 |
| val3 | valA |    r |  4 |