如何在PostgreSQL中定义带[A-Z]{4}[0-9]{4}模式匹配的类型？

嘿，要实现这个格式的类型校验，有两种实用方案，看你的场景需求选就行：

方法一：用DOMAIN创建可复用的自定义类型 #

如果需要在多个表、多个列里重复用这个校验规则，DOMAIN是最优解——它相当于给基础类型套了一层约束规则，能统一管理和复用。

执行下面的SQL创建自定义DOMAIN：

CREATE DOMAIN alphanumeric_code AS TEXT
CHECK (
  VALUE ~ '^[A-Z]{4}[0-9]{4}$'
);

规则说明： #

• alphanumeric_code 是自定义类型的名称，你可以根据业务改成更贴合的名字（比如product_code）
• TEXT 是这个类型的基础载体，因为我们要校验的是字符串格式
• 正则表达式 ^[A-Z]{4}[0-9]{4}$ 确保严格匹配：
  • ^ 和 $ 锚定整个字符串，避免出现“包含符合格式子串但整体不符合”的情况（比如拒绝XYZABCD1234这种长串）
  • [A-Z]{4} 精准匹配4个大写英文字母
  • [0-9]{4} 精准匹配4个阿拉伯数字

如果担心数据库排序规则（collation）影响大小写匹配（比如某些collation下[A-Z]会误匹配小写），可以用POSIX字符类更稳妥：

CREATE DOMAIN alphanumeric_code AS TEXT
CHECK (
  VALUE ~ '^[[:upper:]]{4}[[:digit:]]{4}$'
);

使用示例 #

创建表时直接用这个自定义类型：

CREATE TABLE products (
  product_id alphanumeric_code PRIMARY KEY,
  product_name TEXT NOT NULL
);

插入符合格式的数据会顺利执行：

INSERT INTO products (product_id, product_name) VALUES ('ABCD1234', 'Pro Gaming Laptop');

插入不符合规则的数据会直接触发约束报错：

-- 错误：包含小写字母
INSERT INTO products (product_id, product_name) VALUES ('abcd1234', 'Wireless Headset');
-- 错误：长度不足
INSERT INTO products (product_id, product_name) VALUES ('ABC1234', 'Portable Charger');

方法二：直接给列加CHECK约束 #

如果只是单个列需要这个校验，没必要创建DOMAIN，直接在表定义里加CHECK约束更简单：

CREATE TABLE products (
  product_id TEXT PRIMARY KEY,
  product_name TEXT NOT NULL,
  CHECK (product_id ~ '^[A-Z]{4}[0-9]{4}$')
);

效果和DOMAIN完全一致，只是校验规则只作用于当前列。

额外小提示 #

• 两种方案默认都允许NULL值，如果要强制非空，记得给类型或列加NOT NULL约束
• 要是想让报错信息更友好，可以用触发器自定义提示（比如明确告诉用户格式要求），不过一般CHECK约束的默认报错已经够用。举个触发器的例子：

CREATE OR REPLACE FUNCTION validate_product_id_format()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.product_id !~ '^[A-Z]{4}[0-9]{4}$' THEN
    RAISE EXCEPTION 'Invalid product ID format! Must be 4 uppercase letters + 4 numbers (e.g., ABCD1234). Got: %', NEW.product_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_product_id
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION validate_product_id_format();

内容的提问来源于stack exchange，提问作者Lilac Liu