1

I have a table similar to what is below. I have listed only one column since that's what I'm interested in.

| column 
---------
| foo
| foo
| foo
| bar
| bar
| bar

Below is the output that I desire but I can't seem to figure out how to make it happen. This column is not a primary key or serial key.

| column 
---------
| foo 1
| foo 2
| foo 3
| bar 1
| bar 2
| bar 3

I want to increment the string if there are duplicates.

4

You can use the row number window function for this (http://www.postgresql.org/docs/9.2/static/functions-window.html)

SELECT col || ' ' || row_number() OVER (PARTITION BY col) 
FROM test_a;

Output will be:

+------------+
| ?column?   |
|------------|
| bar 1      |
| bar 2      |
| bar 3      |
| bar 4      |
| foo 1      |
| foo 2      |
| foo 3      |
| foo 4      |
+------------+

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.