Assign values for every unique value in column

Question

I have a table in PostgreSQL with a column "country" in which there are 32 distinct string values, but far more rows

How is it possible to assign every unique string a specific numeric value (e.g. 'Austria' = 1, 'Australia' = 2,..., 'USA' = 32) I could use the UPDATE statement, however, in this case, doing manually 32 countries is not a good way, I guess. I hope there is another way around.

Does your table have 32 rows each one with a different country, or many more rows but only 32 different [and repeated] values? — The Impaler, Oct 12 '18 at 16:37
It has far more rows, but only 32 values in country column, and these values repeat — Ivan.S, Oct 12 '18 at 16:49
I don't think this is a duplicate of the other question. Voting to reopen. — The Impaler, Oct 12 '18 at 16:52

score 1 · Accepted Answer · answered Oct 12 '18 at 16:30

1

In Postgres, here in one method:

update country c
    set col = cc.seqnum
    from (select c.*, row_number() over (order by country) as seqnum
          from country c
         ) cc
    where cc.country = c.country;

answered Oct 12 '18 at 16:30

Gordon Linoff

1,242,037
58
646
786

score -1 · Answer 2 · answered Oct 12 '18 at 16:25

-1

You can easily add a new ID column in the country table to cater for this.

See the syntax below:

Alter Table country
Add Id Int Identity(1, 1)
Go

answered Oct 12 '18 at 16:25

MEdwin

2,940
1
14
27

Assign values for every unique value in column

2 Answers2