How to create a SET-like type on postgresql

Question

I would like to create a column of week days such that we can select more then only one day.

I know the enum type can do that, but it can only contain one item.

How can I create a datatype in PostgreSQL such that I can have something that fuctions like a multiple-choice enum, just like a set on MySQL?

Answer 1

I guess an array is the closest match to the dreaded set data type.

But that solution is not normalized, and you will probably run into several issues because of that. I'd recommend to store that in a properly normalized table, especially if you plan to query on the selected values or do other reporting on that.

Answer 2

Use the HSTORE column type. HSTORE stores key/value pairs. You can use null values if you only care about checking if a key exists.

See https://www.postgresql.org/docs/current/static/hstore.html.

For example, to ask Is 'x' in my hstore?, do

CREATE EXTENSION HSTORE;  --create extension only has to be done once
SELECT * FROM 'x=>null,y=>null,z=>null'::HSTORE ? 'x';

I believe this is operation is O(1). In contrast, checking for containment in an ARRAY-type column, is O(n).

Answer 3

Bit Strings BIT(n) are the closest PostgreSQL has to MySQL's SET types.

For days of the week, use BIT(7).

Unlike MySQL's SET, the width of a BIT type is not constrained to 64 bits or less.