current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 4 down vote favorite
3

Is there any other way to search for a certain value in an integer[] column in Postgres?

My currently installed Postgres version does not allow the following statement:

SELECT * FROM table WHERE values *= 10;

Array examples:

'{11043,10859,10860,10710,10860,10877,10895,11251}'
'{11311,10698,10697,10710,10712,10711,10708}'

The statement should return every row where the array contains '10710'.

share|improve this question
up vote 14 down vote accepted

For equality checks you can simply:

SELECT * FROM table WHERE 10 = ANY (values);

Read about ANY/SOME in the manual.

share|improve this answer
    
Merce, des wos. :) – jussi Nov 23 '11 at 13:28
up vote 0 down vote

quickly search will be so, but you should use index gist or gin for intarray type Postgres intarray

 SELECT * FROM table WHERE values @> ARRAY[10];
share|improve this answer
up vote 0 down vote 
**Store Integer Array as Strings in Postgresql and Query the Array**    
Finally I could save the integer as string array in one column able to successfully convert into array and query the array using below example.

    CREATE TABLE test
    (
      year character varying,
      id serial NOT NULL,
      category_id character varying,
      CONSTRAINT test_pkey PRIMARY KEY (id)
    )

    Data
    "2005";1;"1,2,3,4"
    "2006";2;"2,3,5,6"
    "2006";3;"4,3,5,6"
    "2007";7;"1,2"


    select distinct(id) from test, (select id as cid, unnest(string_to_array(category_id ,  ',')::integer[]) as cat from test) c where c.cid=test.id and cat in (1,2,3);

    Result:
    2
    1
    3
    7
share|improve this answer

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.