SET Type

The SET type is a collection type that contains string members and is defined when a table column is created.

Specifications

1. The number of members of the SET type ranges from 1 to 64. It cannot be defined as an empty set.
2. A member name can contain a maximum of 255 characters. An empty string can be used as a member name. The member name must be a character constant but cannot be a character constant obtained after calculation, for example, SET('a' || 'b', 'c').
3. The member name cannot contain commas (,) and must be unique.
4. Arrays and domain types of the SET type cannot be created.
5. The SET type is supported only when sql_compatibility is set to B.
6. The SET type cannot be used as the data type of columns in column-store tables.
7. The SET type cannot be used as the partition key of a partitioned table.
8. You need to use CASCADE to drop the SET type, and the associated table columns are also dropped.
9. For a USTORE table, if the table contains columns of the SET type and the recycle bin function is enabled, the table is directly deleted instead of being moved to the recycle bin.
10. ALTER TABLE cannot be used to change a column of the SET type to another SET type.
11. When a table or a table column associated with the SET type is deleted, or a table column of the SET type is changed to another type, the SET data type is also deleted.
12. CREATE TABLE { AS | LIKE } cannot be used to create a table containing the SET type.
13. The SET type is created with table columns, and its name is a combination. If a data type with the same name already exists in the schema, the SET type fails to be created.
14. The SET type can be compared with the =, <, >, <, <=, >, and >= of the int2, int4, int8, and text types.
15. The SET type can be converted to the int2, int4, int8, float4, float8, numeric, char, varchar, text and nvarchar2 data types.

Precautions

• The table column values of the SET type must be a subset of the set defined by the SET type. For example:

  CREATE TABLE employee (
    name text,
    site SET('beijing','shanghai','nanjing','wuhan')
  );

• The value of the site column must be a subset of the preceding set definition and can be an empty set. If the provided value does not exist in the members of the SET definition, an error is reported. For example:

  MogDB=# INSERT INTO employee values('zhangsan', 'nanjing,beijing');
  INSERT 0 1
  MogDB=# insert into employee values ('zhangsan', 'hangzhou');
  ERROR:  invalid input value for set employee_site_set: 'hangzhou'
  LINE 1: insert into employee values ('zhangsan', 'hangzhou');
                                                   ^
  CONTEXT:  referenced column: site
  MogDB=#

• Regardless of the sequence of the member values provided by the user, the queried values of the SET type are displayed in the defined sequence after the INSERT operation is successful.

  MogDB=# select * from employee;
     name   |      site       
  ----------+-----------------
   zhangsan | beijing,nanjing
  (1 rows)

• The SET type is stored in bitmap mode. Members of the SET type are assigned different values according to the sequence in which they are defined. For example, the values of SET('beijing','shanghai','nanjing','wuhan') are as follows:

  Table 1 SET members and their corresponding values

  SET Member	Member Value	Binary Value
  'beijing'	1	0001
  'shanghai'	2	0010
  'nanjing'	4	0100
  'wuhan'	8	1000

  Therefore, if a value is assigned to a column of the SET type, the value is converted to the corresponding subset. For example, the binary value corresponding to 9 is 1001, and the corresponding subset is 'beijing,wuhan'.

  MogDB=# INSERT INTO employee values('lisi', 9);
  INSERT 0 1
  MogDB=# select * from employee;
     name   |      site       
  ----------+-----------------
   zhangsan | beijing,nanjing
   lisi     | beijing,wuhan
  (2 rows)