Postgres select where specific array in enum array

Question

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.

Any rows where the animal_whitelist equals dog
Any rows where the animal_whitelist equals dog and cat
Any rows where the animal_whitelist equals dog or cat
Any rows where the animal_whitelist is not dog, cat, or bird

Start with the documentation: postgresql.org/docs/9.6/static/functions-array.html. — Gordon Linoff
– Gordon Linoff, Commented Mar 6, 2018 at 13:11
I have tried a lot of different things otherwise I wouldn't have posted here. It get's weird because it's an array of enums. Contains @> gets me close — Alexander Lallier
– Alexander Lallier, Commented Mar 6, 2018 at 13:13

Ivan Burlutskiy · Accepted Answer · 2018-03-06 13:35:22Z

7

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";

answered Mar 6, 2018 at 13:35

Ivan Burlutskiy

1,6331 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Lallier Over a year ago

Both of our answers worked but since you provided better commenting and an online SQL tool I selected your answer.

Alexander Lallier · Accepted Answer · 2018-03-06 13:32:26Z

1

I think I figured it out with some more experimentation

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

answered Mar 6, 2018 at 13:32

Alexander Lallier

5056 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres select where specific array in enum array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related