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

Question

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?

score 6 · Accepted Answer · 2020-02-21 07:55:52Z

6

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.

edited Feb 21, 2020 at 7:55

answered Feb 21, 2020 at 7:30

user330315

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

2 Comments

Jasen Over a year ago

You could use the enum as the primary key on the small table, that's what I do, it's fairly painless.

nfm Over a year ago

Do you think this is preferable to a function that returned the subset of type values? It feels more visible/discoverable, but maybe less ergonomic. Alternatively, maybe just a view would suffice without the event_type table abstraction!

Laurenz Albe · Accepted Answer · 2020-02-26 12:44:58Z

2

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[])

edited Feb 26, 2020 at 12:44

answered Feb 21, 2020 at 6:59

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related