Checking if a table row has an integer constraint


I’m currently practising SQL concepts and the integer part of the following question has really got me stumped:

"Recall that constraints is a method in PostgreSQL that allows us to maintain data integrity. Specifically, CHECK can be used to implement more precise constraints on our table. We can update an existing table by using ALTER TABLE and adding the CHECK constraint. In the following table performers, we would like to ensure that the session_length column has the following constraints:

  • integer
  • Positive

Using ALTER TABLE, add a CHECK constraint to ensure session_length is positive."

Here’s my code:

ALTER TABLE performers ADD CHECK (session_length.type IS integer AND session_length IS NOT NULL AND session_length > 0);

How do I do an integer check?


This is a weird one because why wouldn’t you just use an integer? If it’s a floating point column that we wanted to keep constrained to an integer we could probably use floor(). But again, why not just make the column an integer. The schema should follow the principle of least surprise. If a dev sees that a column is floating point they will assume first they can put a floating point in, since an integer constraint would be very rare.

Yeah I agree, especially seeing as integer is technically not a constraint, although it acts like one.

I haven’t been able to find the practice question again but if I do, I’ll try the floor() function. Thanks!