Data validation/constraint in Postgres DB

February 4, 2022

I have a (likely) simple question about data validation in a Postgres DB.

I have the following table:

    Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id_number    | integer               |           | not null |         | plain    |              |
 last_name    | character varying(50) |           | not null |         | extended |              |
 first_name   | character varying(50) |           | not null |         | extended |              |
 school       | character varying(50) |           | not null |         | extended |              |
 district     | character varying(50) |           | not null |         | extended |              |

Code to create the table

CREATE TABLE students (
id_number INTEGER PRIMARY KEY NOT NULL,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
school VARCHAR(50) NOT NULL,
district VARCHAR(50) NOT NULL);

I want to create a list of valid input strings (text) for a column and reject any other input.

For example: for the "districts" column, I want the only input allowed to be "district a," district b," or "district c."

I’ve read over the constraints documentation but don’t see anything about text constraints or using "or."

Is this possible? If so, how would I do it?

Thanks

>Solution :

Right at the top of the linked documentation it discusses CHECK constraints, that’s what you want here:

CREATE TABLE students (
    ...
    district VARCHAR(50) NOT NULL CHECK (district in ('district a', 'district b', 'district c')
);

Alternatively, you could add a separate table with the districts and then use a FOREIGN KEY constraint to restrict the districts to only those in the districts table.

For this you’d have something like:

create table districts (
    id integer not null primary key,
    name varchar not null
)

and then:

CREATE TABLE students (
    id_number INTEGER PRIMARY KEY NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    school VARCHAR(50) NOT NULL,
    district_id integer not null references districts(id)
)

and you’d JOIN to the districts table to get the district names.
Using a separate table would make it easier to get a list a possible districts, add new ones, remove old ones, and change the district’s names. This would also be a more normalized approach, might be a little more work at the beginning but it is a big win later on.