How to define minimum and maximum value as range for a postgresql field?

I would like to compress the values that a field can take in my PostgreSQL database, but could not find a way to do it in a query while creating the table.

For instance, let’s say I have a table which is named Books, and it contains some essential information about my books. The fields might be like:

• bookId
• ownerId
• pageNumber

Let’s say I want pageNumber field to have a minimum and maximum values as 1 and 500.

Now, it is easy to squeeze the value scale between 1 and 500, but I want to do it on the database side, which needed to be done over postgreSQL query.

Any help would be appreciated.

Thanks in advance.

Answer

You can add a check constraint:

alter table books add constraint chk_books_pagenumber
    check (pagenumber between 1 and 500);

This limits the values between 1 and 500.