CODEWITHSUNDEEP
postgresqlenums
nfm
nfm

Reputation: 20737

What's the correct abstraction for storing a set of postgres enum values for reuse?

I have the following enum and table:

CREATE TYPE public.event_type as ENUM (
  'eat_apple',
  'eat_banana',
  'brush_teeth'
);

CREATE TABLE public.events (
  id integer not null,
  type public.event_type,
  created_at timestamp without time zone
);

I have a lot of queries that I'm writing against this table that are only concerned with a subset of the event types (eat_apple and eat_banana).

I could write each of these queries with a WHERE type IN ('eat_banana', 'eat_apple') clause, however there's a high likelihood that in future, I'll need to add an eat_pear event type.

What are the available abstractions in Postgres for storing a subset of these enum values for reuse throughout queries, in a way where the subset can be extended in future, such that existing queries take this into account?

Upvotes: 2

Views: 196

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247665

You would use an array of type event_type[] and modify the query to the (equivalent)

WHERE type = ANY (array_value)

If you want to use a string constant, you could write

WHERE type = ANY ('{eat_banana,eat_apple}'::event_type[])

Upvotes: 2

user330315
user330315

Reputation:

I would ditch the enum completely and make event_type a proper table that contains a flag if it's a "default" type. 

create table event_type
(
  id integer primary key,
  name text not null unique,
  is_default_type boolean not null default true
);

create table events 
(
  id integer not null,
  type integer not null references event_type,
  created_at timestamp without time zone
);

To find those with the default subset you can do:

select e.*
from events e
  join event_type et on et.id = e.type and et.is_default_type;

To maintain the default subset, you just change the flag. For convenience you can create a view from the above query, so you never need to worry about the join.

If you create the view using an IN operator instead of the join it's also automatically updateable.

Upvotes: 6

Related Questions