{"Entry":{"collection":"sql","key":"create-domain","name":"CREATE DOMAIN","aliases":["createdomain"],"metadata":{"aliases":["createdomain"],"changed_in":["7.4","9.0","9.1","17"],"changes":[{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters"],"changed":["description","examples","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE DOMAIN name [AS] data_type","[ DEFAULT expression ]","{ NOT NULL | NULL | CHECK (expression) }"],"removed":["CREATE DOMAIN domainname [AS] data_type","[ DEFAULT default_expr ]"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["where constraint is:"]},"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ COLLATE collation ]"],"removed":[]},"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.2"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["parameters","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ domain_constraint [ ... ] ]"],"removed":["[ constraint [ ... ] ]"]},"to":"17"}],"content_hash":"f33a00c3fddfc5cc2fa4c94cf58cf41cd392e28dbc742c7cccdb424302605286","editorial":{},"first_version":"7.3","group":"type","imported_at":"2026-09-30T17:43:37.447375+08:00","last_version":"20","name":"CREATE DOMAIN","object":"DOMAIN","position":3008,"present_in":["7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new domain","purpose_zh":"","related":["alter-domain","drop-domain"],"slug":"create-domain","source_rev":"a709ab85","synopsis":"CREATE DOMAIN name [ AS ] data_type\n[ COLLATE collation ]\n[ DEFAULT expression ]\n[ domain_constraint [ ... ] ]\n\nwhere domain_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | NULL | CHECK (expression) }","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-domain","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-domain","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEDOMAIN","file":"sql-createdomain.html","lang":"en","name":"CREATE DOMAIN","purpose":"define a new domain","purpose_zh":"","related":["alter-domain","drop-domain"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE DOMAIN\u003c/code\u003e creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.\u003c/p\u003e\u003cp\u003eIf a schema name is given (for example, \u003ccode class=\"literal\"\u003eCREATE DOMAIN myschema.mydomain ...\u003c/code\u003e) then the domain is created in the specified schema. Otherwise it is created in the current schema. The domain name must be unique among the types and domains existing in its schema.\u003c/p\u003e\u003cp\u003eDomains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.\u003c/p\u003e\u003cp\u003eTo be able to create a domain, you must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the underlying type.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of a domain to be created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe underlying data type of the domain. This can include array specifiers.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional collation for the domain. If no collation is specified, the domain has the same collation behavior as its underlying data type. The underlying type must be collatable if \u003ccode class=\"literal\"\u003eCOLLATE\u003c/code\u003e is specified.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e clause specifies a default value for columns of the domain data type. The value is any variable-free expression (but subqueries are not allowed). The data type of the default expression must match the data type of the domain. If no default value is specified, then the default value is the null value.\u003c/p\u003e\u003cp\u003eThe default expression will be used in any insert operation that does not specify a value for the column. If a default value is defined for a particular column, it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional name for a constraint. If not specified, the system generates a name.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eValues of this domain are prevented from being null (but see notes below).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eValues of this domain are allowed to be null. This is the default.\u003c/p\u003e\u003cp\u003eThis clause is only intended for compatibility with nonstandard SQL databases. Its use is discouraged in new applications.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e clauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the key word \u003ccode class=\"literal\"\u003eVALUE\u003c/code\u003e to refer to the value being tested. Expressions evaluating to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, an error is reported and the value is not allowed to be converted to the domain type.\u003c/p\u003e\u003cp\u003eCurrently, \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e expressions cannot contain subqueries nor refer to variables other than \u003ccode class=\"literal\"\u003eVALUE\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eWhen a domain has multiple \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints, they will be tested in alphabetical order by name. (\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e versions before 9.5 did not honor any particular firing order for \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints.)\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eDomain constraints, particularly \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));\n\u003c/pre\u003e\u003cp\u003eThe empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.\u003c/p\u003e\u003cp\u003eIt is very difficult to avoid such problems, because of SQL's general assumption that a null value is a valid value of every data type. Best practice therefore is to design a domain's constraints so that a null value is allowed, and then to apply column \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e constraints to columns of the domain type as needed, rather than directly to the domain type.\u003c/p\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e assumes that \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints' conditions are immutable, that is, they will always give the same result for the same input value. This assumption is what justifies examining \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints only when a value is first converted to be of a domain type, and not at other times. (This is essentially the same as the treatment of table \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints, as described in \u003ca href=\"/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS\" title=\"5.5.1. Check Constraints\"\u003eSection 5.5.1\u003c/a\u003e.)\u003c/p\u003e\u003cp\u003eAn example of a common way to break this assumption is to reference a user-defined function in a \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e expression, and then change the behavior of that function. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e does not disallow that, but it will not notice if there are stored values of the domain type that now violate the \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraint. That would cause a subsequent database dump and restore to fail. The recommended way to handle such a change is to drop the constraint (using \u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e), adjust the function definition, and re-add the constraint, thereby rechecking it against stored data.\u003c/p\u003e\u003cp\u003eIt's also good practice to ensure that domain \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e expressions will not throw errors.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eThis example creates the \u003ccode class=\"type\"\u003eus_postal_code\u003c/code\u003e data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US postal code:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE DOMAIN us_postal_code AS TEXT\nCHECK(\n   VALUE ~ '^\\d{5}$'\nOR VALUE ~ '^\\d{5}-\\d{4}$'\n);\n\nCREATE TABLE us_snail_addy (\n  address_id SERIAL PRIMARY KEY,\n  street1 TEXT NOT NULL,\n  street2 TEXT,\n  street3 TEXT,\n  city TEXT NOT NULL,\n  postal us_postal_code NOT NULL\n);\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThe command \u003ccode class=\"command\"\u003eCREATE DOMAIN\u003c/code\u003e conforms to the SQL standard.\u003c/p\u003e\u003cp\u003eThe syntax \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e in this command is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension. (A standard-conforming way to write the same for non-composite data types would be \u003ccode class=\"literal\"\u003eCHECK (VALUE IS NOT NULL)\u003c/code\u003e. However, per \u003ca href=\"/docs/18/sql-createdomain.html#SQL-CREATEDOMAIN-NOTES\" title=\"Notes\"\u003ethe section called “Notes”\u003c/a\u003e, such constraints are best avoided in practice anyway.) The \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003econstraint\u003c/span\u003e”\u003c/span\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension (see also \u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY\" title=\"Compatibility\"\u003eCompatibility\u003c/a\u003e).\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-domain/?v=18\" title=\"ALTER DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\n    [ COLLATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e ]\n    [ DEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ]\n    [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ ... ] ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e is:\u003c/span\u003e\n\n[ CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e ]\n{ NOT NULL | NULL | CHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e) }","synopsis_text":"CREATE DOMAIN name [ AS ] data_type\n[ COLLATE collation ]\n[ DEFAULT expression ]\n[ domain_constraint [ ... ] ]\n\nwhere domain_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | NULL | CHECK (expression) }"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-domain","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE DOMAIN","Summary":"定义一个新域","BodyHTML":"\u003cpre\u003eCREATE DOMAIN name [ AS ] data_type\n[ COLLATE collation ]\n[ DEFAULT expression ]\n[ domain_constraint [ ... ] ]\n\n其中 domain_constraint 是：\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | NULL | CHECK (expression) }\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE DOMAIN\u003c/code\u003e创建一个新域。域本质上是一种带有可选约束（即对允许值集合的限制）的数据类型。定义域的用户将成为其拥有者。\u003c/p\u003e\u003cp\u003e如果给定了模式名（例如\u003ccode\u003eCREATE DOMAIN myschema.mydomain ...\u003c/code\u003e），则该域会在指定模式中创建。否则它会在当前模式中创建。域名在其所在模式中的现有类型和域之间必须唯一。\u003c/p\u003e\u003cp\u003e域适合把字段上的常见约束抽象到单一位置进行维护。例如，若有多个表都包含电子邮件地址列，并且都需要同一个 CHECK 约束来验证地址语法，那么定义一个域会比在每个表上分别设置该约束更合适。\u003c/p\u003e\u003cp\u003e要创建域，你必须对其底层类型拥有\u003ccode\u003eUSAGE\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的域名（可选地带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的底层数据类型。它可以包含数组说明符。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的可选排序规则。如果未指定排序规则，则该域的排序规则行为与其底层数据类型相同。如果指定了\u003ccode\u003eCOLLATE\u003c/code\u003e，则底层类型必须是一种支持排序规则的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDEFAULT \u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eDEFAULT\u003c/code\u003e子句为该域数据类型的列指定默认值。该值可以是任意不含变量的表达式（但不允许子查询）。默认表达式的数据类型必须与该域的数据类型匹配。如果未指定默认值，则默认值为空值。\u003c/p\u003e\u003cp\u003e默认表达式会在任何未为该列指定值的插入操作中使用。如果为某个特定列定义了默认值，它就会覆盖与该域关联的任何默认值。反过来，域默认值又会覆盖与底层数据类型关联的任何默认值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCONSTRAINT \u003cem\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e约束的可选名称。如果未指定，系统会生成一个名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的值不允许为空值（但见下文注解）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的值允许为空值。这是默认行为。\u003c/p\u003e\u003cp\u003e该子句仅用于与非标准 SQL 数据库兼容。不鼓励在新应用中使用它。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCHECK (\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eCHECK\u003c/code\u003e子句指定该域的值必须满足的完整性约束或测试。每个约束都必须是一个产生布尔结果的表达式。它应使用关键字\u003ccode\u003eVALUE\u003c/code\u003e来引用被测试的值。求值结果为 TRUE 或 UNKNOWN 的表达式会通过检查。如果表达式产生 FALSE 结果，就会报告错误，并且不允许将该值转换成该域类型。\u003c/p\u003e\u003cp\u003e当前，\u003ccode\u003eCHECK\u003c/code\u003e表达式不能包含子查询，也不能引用 \u003ccode\u003eVALUE\u003c/code\u003e之外的其他变量。\u003c/p\u003e\u003cp\u003e当一个域有多个\u003ccode\u003eCHECK\u003c/code\u003e约束时，会按名称的字母顺序测试它们。（9.5 之前的\u003cspan\u003ePostgreSQL\u003c/span\u003e版本并不保证\u003ccode\u003eCHECK\u003c/code\u003e约束遵循任何特定的触发顺序。）\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e域约束，特别是\u003ccode\u003eNOT NULL\u003c/code\u003e，会在把值转换成域类型时进行检查。即便存在这样的约束，一个名义上属于该域类型的列也仍可能读出为空值。例如，在外连接查询中，如果该域列位于外连接中可为空的一侧，就可能发生这种情况。一个更微妙的例子是：\u003c/p\u003e\u003cpre\u003eINSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));\n\u003c/pre\u003e\u003cp\u003e这个空标量子 SELECT 会产生一个空值，该空值被视为域类型的值，因此不会再对其执行进一步的约束检查，插入也会成功。\u003c/p\u003e\u003cp\u003e由于 SQL 普遍假定空值是每种数据类型的合法值，因此很难彻底避免这类问题。因此，最佳实践是把域约束设计为允许空值，然后在需要时对该域类型的列应用列级\u003ccode\u003eNOT NULL\u003c/code\u003e约束，而不是直接对域类型应用这种约束。\u003c/p\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e假定\u003ccode\u003eCHECK\u003c/code\u003e 约束的条件是不可变的，也就是说，对于相同的输入值，它们总会给出相同的结果。正是基于这一假设，系统只会在值首次被转换为域类型时检查 \u003ccode\u003eCHECK\u003c/code\u003e约束，而不会在其他时候检查。（这与表 \u003ccode\u003eCHECK\u003c/code\u003e约束的处理方式基本相同，如\u003ca href=\"/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS\" rel=\"nofollow\"\u003e第 5.5.1 节\u003c/a\u003e所述。）\u003c/p\u003e\u003cp\u003e打破这一假设的一种常见方式是，在\u003ccode\u003eCHECK\u003c/code\u003e表达式中引用用户定义函数，然后改变该函数的行为。\u003cspan\u003ePostgreSQL\u003c/span\u003e 不禁止这样做，但如果此时已有存储的域类型值违反了\u003ccode\u003eCHECK\u003c/code\u003e 约束，系统也不会注意到。这会导致后续数据库转储和恢复失败。推荐的处理方式是先删除该约束（使用\u003ccode\u003eALTER DOMAIN\u003c/code\u003e），调整函数定义，然后重新添加约束，从而重新根据已存储数据进行检查。\u003c/p\u003e\u003cp\u003e此外，确保域的\u003ccode\u003eCHECK\u003c/code\u003e表达式不会抛出错误也是一种良好实践。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e这个示例创建\u003ccode\u003eus_postal_code\u003c/code\u003e数据类型，然后在一个表定义中使用该类型。这里使用正则表达式测试来验证该值看起来是否为一个合法的美国邮政编码：\u003c/p\u003e\u003cpre\u003eCREATE DOMAIN us_postal_code AS TEXT\nCHECK(\n   VALUE ~ \u0026#39;^\\d{5}$\u0026#39;\nOR VALUE ~ \u0026#39;^\\d{5}-\\d{4}$\u0026#39;\n);\n\nCREATE TABLE us_snail_addy (\n  address_id SERIAL PRIMARY KEY,\n  street1 TEXT NOT NULL,\n  street2 TEXT,\n  street3 TEXT,\n  city TEXT NOT NULL,\n  postal us_postal_code NOT NULL\n);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e命令\u003ccode\u003eCREATE DOMAIN\u003c/code\u003e符合 SQL 标准。\u003c/p\u003e\u003cp\u003e本命令中的\u003ccode\u003eNOT NULL\u003c/code\u003e语法是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。（对于非复合数据类型，符合标准的等价写法是\u003ccode\u003eCHECK (VALUE IS NOT NULL)\u003c/code\u003e。不过，正如\u003ca href=\"/docs/18/sql-createdomain.html#SQL-CREATEDOMAIN-NOTES\" title=\"注解\" rel=\"nofollow\"\u003e“注解”一节\u003c/a\u003e所述，这类约束在实践中最好还是避免使用。）\u003ccode\u003eNULL\u003c/code\u003e\u003cspan\u003e“\u003cspan\u003e约束\u003c/span\u003e”\u003c/span\u003e也是\u003cspan\u003ePostgreSQL\u003c/span\u003e扩展（另见\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY\" title=\"兼容性\" rel=\"nofollow\"\u003eCompatibility\u003c/a\u003e）。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-domain/?v=18\" title=\"ALTER DOMAIN\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"5a44bb2c119387fed40da2a5f5dbc71654d4e168cb4802ecbad2bf53e4ef22dc","Payload":{"purpose_zh":"定义一个新域","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE DOMAIN\u003c/code\u003e创建一个新域。域本质上是一种带有可选约束（即对允许值集合的限制）的数据类型。定义域的用户将成为其拥有者。\u003c/p\u003e\u003cp\u003e如果给定了模式名（例如\u003ccode class=\"literal\"\u003eCREATE DOMAIN myschema.mydomain ...\u003c/code\u003e），则该域会在指定模式中创建。否则它会在当前模式中创建。域名在其所在模式中的现有类型和域之间必须唯一。\u003c/p\u003e\u003cp\u003e域适合把字段上的常见约束抽象到单一位置进行维护。例如，若有多个表都包含电子邮件地址列，并且都需要同一个 CHECK 约束来验证地址语法，那么定义一个域会比在每个表上分别设置该约束更合适。\u003c/p\u003e\u003cp\u003e要创建域，你必须对其底层类型拥有\u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的域名（可选地带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的底层数据类型。它可以包含数组说明符。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的可选排序规则。如果未指定排序规则，则该域的排序规则行为与其底层数据类型相同。如果指定了\u003ccode class=\"literal\"\u003eCOLLATE\u003c/code\u003e，则底层类型必须是一种支持排序规则的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e子句为该域数据类型的列指定默认值。该值可以是任意不含变量的表达式（但不允许子查询）。默认表达式的数据类型必须与该域的数据类型匹配。如果未指定默认值，则默认值为空值。\u003c/p\u003e\u003cp\u003e默认表达式会在任何未为该列指定值的插入操作中使用。如果为某个特定列定义了默认值，它就会覆盖与该域关联的任何默认值。反过来，域默认值又会覆盖与底层数据类型关联的任何默认值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e约束的可选名称。如果未指定，系统会生成一个名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的值不允许为空值（但见下文注解）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的值允许为空值。这是默认行为。\u003c/p\u003e\u003cp\u003e该子句仅用于与非标准 SQL 数据库兼容。不鼓励在新应用中使用它。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e子句指定该域的值必须满足的完整性约束或测试。每个约束都必须是一个产生布尔结果的表达式。它应使用关键字\u003ccode class=\"literal\"\u003eVALUE\u003c/code\u003e来引用被测试的值。求值结果为 TRUE 或 UNKNOWN 的表达式会通过检查。如果表达式产生 FALSE 结果，就会报告错误，并且不允许将该值转换成该域类型。\u003c/p\u003e\u003cp\u003e当前，\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e表达式不能包含子查询，也不能引用 \u003ccode class=\"literal\"\u003eVALUE\u003c/code\u003e之外的其他变量。\u003c/p\u003e\u003cp\u003e当一个域有多个\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束时，会按名称的字母顺序测试它们。（9.5 之前的\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e版本并不保证\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束遵循任何特定的触发顺序。）\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e域约束，特别是\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e，会在把值转换成域类型时进行检查。即便存在这样的约束，一个名义上属于该域类型的列也仍可能读出为空值。例如，在外连接查询中，如果该域列位于外连接中可为空的一侧，就可能发生这种情况。一个更微妙的例子是：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));\n\u003c/pre\u003e\u003cp\u003e这个空标量子 SELECT 会产生一个空值，该空值被视为域类型的值，因此不会再对其执行进一步的约束检查，插入也会成功。\u003c/p\u003e\u003cp\u003e由于 SQL 普遍假定空值是每种数据类型的合法值，因此很难彻底避免这类问题。因此，最佳实践是把域约束设计为允许空值，然后在需要时对该域类型的列应用列级\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e约束，而不是直接对域类型应用这种约束。\u003c/p\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e假定\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e 约束的条件是不可变的，也就是说，对于相同的输入值，它们总会给出相同的结果。正是基于这一假设，系统只会在值首次被转换为域类型时检查 \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束，而不会在其他时候检查。（这与表 \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束的处理方式基本相同，如\u003ca href=\"/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS\" title=\"5.5.1. 检查约束\"\u003e第 5.5.1 节\u003c/a\u003e所述。）\u003c/p\u003e\u003cp\u003e打破这一假设的一种常见方式是，在\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e表达式中引用用户定义函数，然后改变该函数的行为。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 不禁止这样做，但如果此时已有存储的域类型值违反了\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e 约束，系统也不会注意到。这会导致后续数据库转储和恢复失败。推荐的处理方式是先删除该约束（使用\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e），调整函数定义，然后重新添加约束，从而重新根据已存储数据进行检查。\u003c/p\u003e\u003cp\u003e此外，确保域的\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e表达式不会抛出错误也是一种良好实践。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e这个示例创建\u003ccode class=\"type\"\u003eus_postal_code\u003c/code\u003e数据类型，然后在一个表定义中使用该类型。这里使用正则表达式测试来验证该值看起来是否为一个合法的美国邮政编码：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE DOMAIN us_postal_code AS TEXT\nCHECK(\n   VALUE ~ '^\\d{5}$'\nOR VALUE ~ '^\\d{5}-\\d{4}$'\n);\n\nCREATE TABLE us_snail_addy (\n  address_id SERIAL PRIMARY KEY,\n  street1 TEXT NOT NULL,\n  street2 TEXT,\n  street3 TEXT,\n  city TEXT NOT NULL,\n  postal us_postal_code NOT NULL\n);\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e命令\u003ccode class=\"command\"\u003eCREATE DOMAIN\u003c/code\u003e符合 SQL 标准。\u003c/p\u003e\u003cp\u003e本命令中的\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e语法是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。（对于非复合数据类型，符合标准的等价写法是\u003ccode class=\"literal\"\u003eCHECK (VALUE IS NOT NULL)\u003c/code\u003e。不过，正如\u003ca href=\"/docs/18/sql-createdomain.html#SQL-CREATEDOMAIN-NOTES\" title=\"注解\"\u003e“注解”一节\u003c/a\u003e所述，这类约束在实践中最好还是避免使用。）\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e约束\u003c/span\u003e”\u003c/span\u003e也是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展（另见\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY\" title=\"兼容性\"\u003eCompatibility\u003c/a\u003e）。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-domain/?v=18\" title=\"ALTER DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\n    [ COLLATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e ]\n    [ DEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ]\n    [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ ... ] ]\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e 是：\u003c/span\u003e\n\n[ CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e ]\n{ NOT NULL | NULL | CHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e) }","synopsis_text":"CREATE DOMAIN name [ AS ] data_type\n[ COLLATE collation ]\n[ DEFAULT expression ]\n[ domain_constraint [ ... ] ]\n\n其中 domain_constraint 是：\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | NULL | CHECK (expression) }"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}