current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 0 down vote favorite

I have a postres table that looks like such: (shortened)

id (serial) | col1 (character varying[])
----------------------------------------
1           | {'Life', 'Health', "'VA Data'"}

I am trying to do the following:

SELECT * FROM mytable WHERE 'Life' = ANY (col1)

The results of this query, is zero records.

The goal being, I want any row, that has the value 'Life' within the col1 array.

What am I doing wrong?

share|improve this question
up vote 2 down vote accepted

Any should work, but what you are showing me for output for the table is not Life but 'Life'. See the examples below (1. correctly inserted data and what it looks like; 2. incorrectly inserted data and what it looks like-looks like yours; 3. all data):

testdb=# select * from test where 'Life' = ANY(col1);
 id |          col1           
----+-------------------------
  1 | {Life,Health,"VA Data"}
(1 row)

testdb=# select * from test where '''Life''' = ANY(col1);
 id |           col1            
----+---------------------------
  2 | {'Life',Health,"VA Data"}
(1 row)

testdb=# select * from test;
 id |           col1            
----+---------------------------
  1 | {Life,Health,"VA Data"}
  2 | {'Life',Health,"VA Data"}
share|improve this answer
    
You are right, data is badly inserted... – Eduardo Dec 10 '15 at 1:14
    
Just to confirm: It should be: {Life, Health, VA Data} with no quotes around anything? – triunenature Dec 10 '15 at 1:18
1  
This is what the insertion of the correct data would look like: insert into test(id, col1) values ('1', ARRAY['Life', 'Health', 'VA Data']); See output of query 1 in answer for what it will look like in output. – Benjamin Bau Dec 10 '15 at 2:54
    
@BenjaminBau, say, I have a db with 1.5 million records inserted incorrectly.... but there are only 15 categories... would there be any easy way to correct this mistake with a fancy update command? (or 15 update commands, one per category?) – triunenature Dec 10 '15 at 18:01
    
I don't know that this can be accomplished with an update statement (unless you know what is in the array for each record). You would have to replace the arrays wholesale. In your position, I would write a stored procedure that iterates through the records, within each record iterates through the array and creates a correctly formatted array that you can update the record to. – Benjamin Bau Dec 11 '15 at 13:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.