How to create a type of SET type on postgresql

Question

I would like to create an add column of days of the week so that we can select more than one day,

I know that the enum type can do this, but we can only select one element

So, how can I create a data type in PostgreSQL so that we can have an enum with multiple choices, like a set in mysql?

Thank you so much.

Answer 1

I assume the array is the closest to the data type of the horrible set.

But this solution is not standardized, and because of this, you are likely to encounter several problems. I would recommend storing this in a properly normalized table, especially if you plan to query the selected values ​​or do other reports.

Answer 2

Use the HSTORE column HSTORE . HSTORE stores key / value pairs. You can use null values ​​if you only need to check if the key exists.

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

For example, 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 O (1) operation. In contrast, the localization check in the ARRAY -type column is O (n).

Answer 3

BIT(n) bit strings are the closest PostgreSQL to MySQL SET types.

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

Unlike MySQL SET , the width of the BIT type is not limited to 64 bits or less.

Answer 4

Define a data type that stores the selected values ​​as a comma-separated string. Given the framework you use to access the database, it's relatively easy to write a TypeDef that converts this string to and from Set (see for example UserType in Hibernate).

You will need to specify which operators you will need in the database. How will sort the work? Is there an extra (+) operator? All of this can be perfectly implemented in Postgres, but you will have to read all the documents for yourself.