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

list of values not in Array column Postgress – Postgresql

  1. I have a array column in postgress table
  2. I have list of values

Want to skip all the rows which have any of the value from my list

Id   Subjects
1    [English, SST]
2    [Science, Maths]
3    [Hindi, Sanskrit]

Want to select rows where subject does not conatin any of [English, hindi, sanskrit] hence outpur should be only id = 2

Tags:

2

Answers


  1. 0

    Having

    create table tt
("Id" int,"Subjects" text[]); 

insert into tt values
(1,    array['English', 'SST']),
(2,    array['Science', 'Maths']),
(3,    array['Hindi', 'Sanskrit']);

    Then

    select "Id" from tt 
where not "Subjects" && array['English', 'Hindi', 'Sanskrit'];

    With the array operator overlaps (elements in common).
    Fiddle

    Login or Signup to reply.
  2. 0

    The comparison should be either

    NOT subjects && list

    if NULL arrays (subjects or list) should be treated as unknown,
    or

    subjects && list IS NOT TRUE

    if NULL arrays should be treated as an empty array.

    The first option returns NULL if either, or both, subjects and list are NULL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search