Update column based on array search in postgresql

Question

id | name                   | status
---+------------------------+--------------------------------
1  | {'Tom','Jerry','Hary'} | { 'Waiting','Waiting',Waiting'}

I want to update status column based on name.

For example, I want to update status column to "Arrived" for name "Hary"

id | name                   | status
---+------------------------+--------------------------------
1  | {'Tom','Jerry','Hary'} | { 'Waiting',Waiting','Arrived'}

Note: I can achieve by using below query. But i don't want to use index positioning. Is there any alternative to achieve above result?

UPDATE table
SET status[3] = 'Arrived'
WHERE name[3]='Hary'

You should really normalize your table and store every array element in one separate record — S-Man
– S-Man, Commented Dec 10, 2019 at 15:20
Quote from the manual Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements — user330315
– user330315, Commented Dec 10, 2019 at 15:21
Just because you can store arrays in tables does not mean you should. This feature exists, but I have yet to see a usecase where it is not better to use a normalized joined table. — Hogan
– Hogan, Commented Dec 10, 2019 at 15:21
@Hogan: I think the "check list" when array may be used is simple: if you always treat the array as one single value in SQL, then it's probably OK to use them. — user330315
– user330315, Commented Dec 10, 2019 at 15:23
@a_horse_with_no_name -- if I understand your use case correctly then it fails in the same way, if you want to add or remove something from the checklist you have to change the whole thing -- which is not really how checklists are supposed to work. Also if you want to check if some item is on the checklist you can't. Basically it is not a checklist it a text field. — Hogan
– Hogan, Commented Dec 10, 2019 at 15:29

GMB · Accepted Answer · 2019-12-10 15:23:06Z

2

As commented by S-Man, you should just fix your data model and store each name/status on a separate record.

Otherwise, you can use array_position():

update table set status[ array_position(name, 'Harry') ] = 'Arrived'
where 'Harry' = any(name)

answered Dec 10, 2019 at 15:23

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Hogan · Accepted Answer · 2019-12-10 15:26:43Z

0

GMB has shown you how to do it, but I think you should also see a normalized design (just in case you don't know how that would look):

Name table
-----------
id     int
name   varchar(70)

Status table
------------
id     int
status varchar(100)

namestatus table
----------------
nameid   int
statusid int

Data

name table
1 Tom
2 Jerry
3 Hary

status table
1 Waiting
2 Arrived

namestatus table
1    1
2    1
3    1

Update statement

UPDATE namestatus
set statusid = 2 
where nameid = 3

answered Dec 10, 2019 at 15:26

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Collectives™ on Stack Overflow

Update column based on array search in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related