Update column of a table based on array of varchar in PostgreSQL

Question

I have a table to which i have added a column varchar colorcode.

The table already has many rows. state of table after adding colorcode column is

id   name               location     colorcode
121  Royal Challengers  Bangalore     
122  Sun Risers         Hyderabad
123  Dare Devils        Delhi
124  Gujrat Lions       Ahmadabad

I have a array of color codes

 ["#FF8484", "#FF82A9", "#FA82FF", "#C682FF", "#8782FF"]

For each row in the table I must update the colorcode column by matching index of array with (row_number() - 1).

I have dealt with list of values when using "in" clause.

example:

select * from table where id in(1,2,3,4) etc

here 1,2,3,4 is nothing but a array

I want to update the colorcode column on similar lines but I don't know how to access the elements of my array based in index.

after running the update statement my expected output is

id   name               location     colorcode
121  Royal Challengers  Bangalore     #FF8484
122  Sun Risers         Hyderabad     #FF82A9
123  Dare Devils        Delhi         #FA82FF
124  Gujrat Lions       Ahmedabad     #C682FF

I can sort the result based on id ie., the primary key

Note: I am using Postgres

What is the expected outcome of the update? Why do you need to refer to the previous row? And how is "previous" defined? On which attribute can you sort the rows? edit your question and add some sample data and the expected result (formatted text please, no screen shots) — user330315
– user330315, Commented Apr 30, 2016 at 8:29
I have edited the question and added the expected outcome of update. I need not refer to previous row. The row_number() - 1 is to make sure that the colorcode corresponds to array index row_number() is 1 based and array indexes are 0 based(i think) so to compensate for that i am doing row_number() -1 . You can sort the rows based on id — Kashif Imran
– Kashif Imran, Commented Apr 30, 2016 at 9:38

trincot · Accepted Answer · 2016-05-01 15:05:35Z

2

You could do it as follows. I assume your table is called t:

update t
set    colorcode = middle.cc
from   (
        select id, ('{#FF8484,#FF82A9,#FA82FF,#C682FF,#8782FF}'::text[])[rn] as cc
        from   (select id, row_number() over (order by id) as rn from t) as base
        where  rn <= 5
       ) as middle
where middle.id = t.id;

In the most inner query (base), the row number is retrieved for each record in the table. Then in the middle query (middle) that row number is used to fetch the corresponding colour code from an in-line array of text elements. Finally the update statement joins that result with the table t again, by its id, in order to store that colour code.

edited May 1, 2016 at 15:05

answered Apr 30, 2016 at 10:19

trincot

357k38 gold badges282 silver badges338 bronze badges

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

2 Comments

trincot Over a year ago

@curiosa, did this solve your issue? Could you give some feed-back?

Kashif Imran Over a year ago

Sorry for late reply, Brilliant answer @trincot this is exactly what i was looking for

Collectives™ on Stack Overflow

Update column of a table based on array of varchar in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related