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?
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).
hstore's underlying storage is actually just an array of k/v pairs unfortunately. I'm my tests ?| on hstore is 2-4 times slower than using an array with the && operator.
HSTORE has been considered inferior to JSONB for a while now: https://dba.stackexchange.com/questions/115825/jsonb-with-indexing-vs-hstore
hstore is that it is only defined for TEXT keys and values. If you want to use a "set" of numbers (e.g. IDs), you need to represent them as text. Whether you use base-10 or base-64 encoding, there's quite a bit of overhead in space and CPU.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.
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.
To emulate a set, you can use an array with a check for items' uniqueness.
A generic example:
CREATE OR REPLACE FUNCTION is_set(a text[]) RETURNS bool
IMMUTABLE
RETURNS NULL ON NULL INPUT
LANGUAGE SQL
AS $$
SELECT NOT EXISTS(
SELECT i
FROM unnest(a) AS t(i)
GROUP BY i
HAVING COUNT(*) > 1
)
$$;
CREATE TABLE with_set_column(
set_column TEXT[] CHECK (is_set(set_column))
);
-- OK
INSERT INTO with_set_column(set_column) VALUES (ARRAY['a', 'b']);
-- Error, not a set
INSERT INTO with_set_column(set_column) VALUES (ARRAY['a', 'b', 'a']);
For the days of week, obviously, you should change text to a designated enum or number type.
