PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to fetch column data having character varying[] column type in postgre sql?

|         Column A         |
      character varying[]
| ------------------------ | 
| {value1,value2,value3}   |

Tried with select * from some_table where columnA LIKE '%value1%‘ but it returns error.

Then tried filtering expected value by using select columnA from some_table but now it returns nothing.

Tags:

3

Answers


  1. 0

    Unnest columnA array and check if the target value (value11 in the example below) exists. t CTE is a mimic of the real table.

    with t(columna) as (
 values 
 ('{value11,value12,value13}'::text[]),
 ('{value21,value22,value23}'),
 ('{value31,value32,value33}')
)
select * from t 
where exists (select from unnest(columna) el where el = 'value11');
    Login or Signup to reply.
  2. 0

    You should to use = ANY(array) predicate

    (2024-07-23 06:59:40) postgres=# select * from foo;
┌───────────────────────────┐
│             a             │
╞═══════════════════════════╡
│ {value1,value2,value3}    │
│ {value10,value20,value30} │
└───────────────────────────┘
(2 rows)

(2024-07-23 06:59:55) postgres=# select * from foo where 'value1' = any(a);
┌────────────────────────┐
│           a            │
╞════════════════════════╡
│ {value1,value2,value3} │
└────────────────────────┘
(1 row)

    See related doc https://www.postgresql.org/docs/current/functions-array.html

    Login or Signup to reply.
  3. 0

    If you want to use indexes, you’d query like this:

    SELECT ...
FROM some_table
WHERE columna @> ARRAY['value1];

    The index would be

    CREATE INDEX ON some_table USING gin (columna);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search