PostgreSQL 查询约束中允许的值列表？

给定一个名为的 PostgreSQL 表requests有一列名为status和这样的约束：

ALTER TABLE requests ADD CONSTRAINT allowed_status_types
  CHECK (status IN (
    'pending', -- request has not been attempted
    'success', -- request succeeded
    'failure'  -- request failed
  ));

In psql我可以像这样提取有关此约束的信息：

example-database=# \d requests
                                          Table "public.example-database"
        Column        |            Type             |                             Modifiers
----------------------+-----------------------------+-------------------------------------------------------------------
 id                   | integer                     | not null default nextval('requests_id_seq'::regclass)
 status               | character varying           | not null default 'pending'::character varying
 created_at           | timestamp without time zone | not null
 updated_at           | timestamp without time zone | not null

Indexes:
    "requests_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "allowed_status_types" CHECK (status::text = ANY (ARRAY['pending'::character varying, 'success'::character varying, 'failure'::character varying]::text[]))

但是是否可以编写一个专门返回allowed_status_types待定、成功、失败？

如果能够在我的应用程序中记住此查询的结果，而不是必须维护重复的副本，那就太棒了。

以您的设计为基础

为了简化事情，我将提供允许的值（100％等效）数组字面量而不是IN表达式（转换为笨拙的 ARRAY 构造函数）：

ALTER TABLE requests ADD CONSTRAINT allowed_status_types
  CHECK (status = ANY ('{pending, success, failure}'::text[]));

系统列中的结果文本pg_constraint.consrc http://www.postgresql.org/docs/current/interactive/catalog-pg-constraint.html:

((status)::text = ANY ('{pending,success,failure}'::text[]))

现在提取花括号之间的列表很简单substring():

SELECT substring(consrc from '{(.*)}') AS allowed_status_types
FROM   pg_catalog.pg_constraint
WHERE  conrelid = 'public.requests'::regclass  -- schema qualify table name!
AND    conname = 'allowed_status_types';  -- we *know* the constraint name

Result:

  allowed_status_types
-------------------------
 pending,success,failure

替代设计

我真正想做的是规范化另一个步骤：

CREATE TABLE request_status (
  status_id "char" PRIMARY KEY
, status text UNIQUE NOT NULL
, note text
);

INSERT INTO request_status(status_id, status, note) VALUES
  ('p', 'pending', 'request has not been attempted')
, ('s', 'success', 'request succeeded')
, ('f', 'failure', 'req');

CREATE TABLE requests (
  id         serial PRIMARY KEY
, status_id "char" NOT NULL DEFAULT 'p' REFERENCES request_status
, created_at timestamp NOT NULL
, updated_at timestamp NOT NULL
);

The 数据类型"char" http://www.postgresql.org/docs/current/interactive/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE是一个单字节 ASCII 字符，非常适合廉价枚举少数可能值。

行的大小现在是 48 个字节，而不是 56 个字节。详细信息请参见此处。 https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes/13570853#13570853
检查允许的状态很简单：

SELECT status FROM request_status