CODEWITHSUNDEEP
sqlpostgresql
Alexander Lallier
Alexander Lallier

Reputation: 495

Postgres select where specific array in enum array

Given this example:

Table:

CREATE TABLE public.animals
(
  name character varying(64),
  whitelist animal_whitelist[]
)

Custom enum type animal_whitelist:

CREATE TYPE public.animal_whitelist AS ENUM
  ('dog',
  'cat',
  'bird');

How can I select specifically to the white list.

In pseudo code this is what I would like to select.

Upvotes: 2

Views: 3729

Answers (2)

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

Please, check this one:

    insert into animals ("name", "whitelist") values ('bobic', array['dog']::animal_whitelist[]);
insert into animals ("name", "whitelist") values ('barsic', array['cat']::animal_whitelist[]);
insert into animals ("name", "whitelist") values ('pet', array['dog', 'cat', 'bird']::animal_whitelist[]);
insert into animals ("name") values ('jim');


-- Any rows where the animal_whitelist equals dog
select * from animals where  array['dog']::animal_whitelist[] = "whitelist" ;

-- Any rows where the animal_whitelist equals dog and cat
select * from animals where  array['dog', 'cat']::animal_whitelist[] = "whitelist" ;

-- Any rows where the animal_whitelist equals dog or cat
select * from animals where  array['dog', 'cat']::animal_whitelist[] <@ "whitelist" ;

-- Any rows where the animal_whitelist is not dog, cat, or bird
select * from animals where not array['dog', 'cat', 'bird']::animal_whitelist[] && "whitelist";

Upvotes: 7

Alexander Lallier
Alexander Lallier

Reputation: 495

I think I figured it out with some more experimentation

  1. SELECT * FROM animals WHERE whitelist @> ARRAY['dog'::whitelist]

  2. SELECT * FROM animals WHERE whitelist @> ARRAY['dog'::whitelist, 'cat'::whitelist] AND NOT whitelist @> ARRAY['bird'::whitelist]

  3. SELECT * FROM animals WHERE whitelist @> ARRAY['dog'::whitelist] OR whitelist @> ARRAY['cat'::whitelist]

  4. SELECT * FROM animals WHERE NOT whitelist @> ARRAY['dog'::whitelist, 'cat'::whitelist, 'bird'::whitelist]

Upvotes: 1

Related Questions