PostgreSQL query for a list of allowed values in a constraint?

Question

Given a PostgreSQL table named requests with a column named status and a constraint like this:

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 I can pull up information about this constraint like this:

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[]))

But is it possible to write a query that specifically returns the allowed_status_types of pending, success, failure?

It would be great to be able to memoize the results of this query within my application, vs. having to maintain a duplicate copy.

Community · Accepted Answer · 2017-05-23 12:01:40Z

Building on your design

To simplify things, I would provide allowed values as (100 % equivalent) array literal instead of the IN expression (that is converted into a clumsy ARRAY constructor):

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

The resulting text in the system column pg_constraint.consrc:

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

Now it's simple to extract the list between curly braces with 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

Alternative design

What I really would do is normalize one more step:

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 data type "char" is a single one-byte ASCII character that's ideal for cheap enumeration of a handful possible values.

The size of your row is now 48 instead of 56 bytes. Details here.
And it's trivial to check allowed statuses:

SELECT status FROM request_status

klin · Accepted Answer · 2016-01-27 01:16:10Z

You can query the system catalog pg_constraint, e.g.:

select consrc
from pg_constraint
where conrelid = 'requests'::regclass
and consrc like '(status%';

                                  consrc                                   
---------------------------------------------------------------------------
 (status = ANY (ARRAY['pending'::text, 'success'::text, 'failure'::text]))
(1 row)

Use the following function to unpack the string:

create or replace function get_check_values(str text)
returns setof text language plpgsql as $$
begin
    return query
        execute format (
            'select * from unnest(%s)',
            regexp_replace(str, '.*(ARRAY\[.*\]).*', '\1'));
end $$;

select get_check_values(consrc)
from pg_constraint
where conrelid = 'requests'::regclass
and consrc like '(status%';

 get_check_values 
------------------
 pending
 success
 failure
(3 rows)

guettli · Accepted Answer · 2017-02-07 12:14:59Z

0

You can create a new table status_types and migrate your column status of your table request to be a ForeignKey to table status_types.

This way you have the usual integrity checks - and it is a portable solution, which works in other relational DBs.

This way it easy to write a query that specifically returns the allowed values.

And it is very easy to extend the list of allowed status types.

answered Feb 7, 2017 at 12:14

guettli

27.7k109 gold badges423 silver badges779 bronze badges

Comments

Amjad Abujamous · Accepted Answer · 2024-01-28 06:48:36Z

consrc was removed in Postgres 12+. If someone encounters this problem with those versions, then the below is the solution:

One can use the following query to list constraints and their details for a certain table.

SELECT
    conname AS constraint_name,
    contype AS constraint_type,
    conkey AS constrained_columns,
    confkey AS referenced_columns,
    confrelid::regclass AS referenced_table,
    conrelid::regclass AS constrained_table,
    CASE
        WHEN contype = 'f' THEN 'FOREIGN KEY'
        WHEN contype = 'u' THEN 'UNIQUE'
        WHEN contype = 'p' THEN 'PRIMARY KEY'
        WHEN contype = 'c' THEN 'CHECK'
    END AS constraint_category,
    CASE
        WHEN contype = 'c' THEN
            (SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE oid = c.oid)
    END AS check_expression
FROM
    pg_constraint c
WHERE
    conrelid = 'table_name'::regclass;

And to get all of the constraints for all tables in the schema, the query below can do it.

SELECT
    conname AS constraint_name,
    conrelid::regclass AS constrained_table,
    CASE 
        WHEN contype = 'c' THEN (SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE oid = c.oid)
    END AS check_expression
FROM
    pg_constraint c
WHERE
    contype = 'c'
AND 
    conname NOT LIKE 'pg%'
AND
    LENGTH(conrelid::VARCHAR) > 1

As one notes, the query simply fetches the data from the table pg_constraint and formulates it properly.

Collectives™ on Stack Overflow

PostgreSQL query for a list of allowed values in a constraint?

4 Answers 4

Building on your design

Alternative design

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Building on your design

Alternative design

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related