current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a table where one column has an array - but stored in a text format:

mytable

id  ids
--  -------
1   '[3,4]'
2   '[3,5]'
3   '[3]'
etc ...

I want to find all records that have the value 5 as an array element in the ids column.

I was trying to achieve this by using the "string to array" function and removing the [ symbols with the translate function, but couldn't find a way.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

You can do this: http://www.sqlfiddle.com/#!1/5c148/12

select *
from tbl
where translate(ids, '[]','{}')::int[] && array[5];

Output:

| ID |   IDS |
--------------
|  2 | [3,5] |

You can also use bool_or: http://www.sqlfiddle.com/#!1/5c148/11

with a as
(
  select id, unnest(translate(ids, '[]','{}')::int[]) as elem
  from tbl
)
select id
from a
group by id
having bool_or(elem = 5);

To see the original elements:

with a as
(
  select id, unnest(translate(ids, '[]','{}')::int[]) as elem
  from tbl
)
select id, '[' || array_to_string(array_agg(elem), ',') || ']' as ids
from a
group by id
having bool_or(elem = 5);

Output:

| ID |   IDS |
--------------
|  2 | [3,5] |

Postgresql DDL is atomic, if it's not late yet in your project, just structure your stringly-typed array to a real array: http://www.sqlfiddle.com/#!1/6e18c/2

alter table tbl
add column id_array int[];

update tbl set id_array = translate(ids,'[]','{}')::int[];

alter table tbl drop column ids;

Query:

select *
from tbl
where id_array && array[5]

Output:

| ID | ID_ARRAY |
-----------------
|  2 |      3,5 |

You can also use contains operator: http://www.sqlfiddle.com/#!1/6e18c/6

select *
from tbl
where id_array @> array[5];

I prefer the && syntax though, it directly connotes intersection. It reflects that you are detecting if there's an intersection between two sets(array is a set)

http://www.postgresql.org/docs/8.2/static/functions-array.html

share|improve this answer
    
Thanks a lot, its actually not that late, i will try to convince that this field gets changed to a real array, thanks a lot for the help –  Casca Rrabias Sep 17 '12 at 20:09
add comment
up vote 3 down vote

If you store the string representation of your arrays slightly differently, you can cast to array of integer directly:

INSERT INTO mytable
VALUES 
 (1, '{3,4}')
,(2, '{3,5}')
,(3, '{3}');

SELECT id, ids::int[]
FROM   mytable;

Else, you have to put in one more step:

SELECT (translate(ids, '[]','{}'))::int[]
FROM   mytable

I would consider making the column an array type to begin with.

Either way, you can find your row like this:

SELECT id, ids 
FROM  (
    SELECT id, ids, unnest(ids::int[]) AS elem
    FROM   mytable
    ) x
WHERE  elem = 5
share|improve this answer
1  
agreed, though if he can edit the database structure he would be better off storing those IDs in separate fields entirely. –  sudowned Sep 15 '12 at 0:02
1  
@sudowned: Or even in a separate table with one number per row. But we really don't have enough information to tell for sure. –  Erwin Brandstetter Sep 15 '12 at 0:11
add comment

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.