Data validation/constraint in Postgres DB

Question

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

mu is too short · Accepted Answer · 2022-02-04 22:32:52Z

3

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.

edited Feb 4, 2022 at 22:32

answered Feb 4, 2022 at 20:33

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gsgilbert Over a year ago

I apologize for not responding via comment until now. Your solution worked great. Thank you.

Collectives™ on Stack Overflow

Data validation/constraint in Postgres DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related