CODEWITHSUNDEEP
postgresql
ADB
ADB

Reputation: 719

How to represent rating in PostgreSQL?

I have a Ratings table which has 3 columns: location_id, rating, user_id (where user_id and location_id are foreign keys in other tables). I'd like a constraint on on rating so that it can only be 0, 1, 2, 3, 4, 5.

My first thought was 

CREATE TYPE rating as ENUM('0', '1', '2', '3', '4', '5')

Is this a good way to represent ratings, or are there better or more efficient ways to store ratings please?

I'm new to SQL so apologies if I've missed something obvious but I couldn't find this question elsewhere.

Upvotes: 1

Views: 1230

Answers (1)

user330315
user330315

Reputation:

enums are rarely a good choice.

If you want to restrict an integer column to a specific range, use a check constraint:

alter table ratings
   add constraint check_rating
   check (rating between 0 and 5);

Upvotes: 2

Related Questions