[FIXED] What data type to use for ratings in PostgreSQL

Issue

I am making a table right now, and I'm confused about what to use, because I used to use smallint(6) but it doesn't work in PostgreSQL.

Solution

If the column can only have integer values between 1 and 5 you can use a SMALLINT for it with a CHECK constraint.

For example:

create table review (
  rating smallint not null check (rating between 1 and 5)
);

The NOT NULL constraint ensures the column always has values.

The CHECK constraint ensures values are always between 1 and 5, and that, for example, a value 6 won't be accepted.

Answered By - The Impaler
Answer Checked By - Cary Denson (JavaFixing Admin)