CODEWITHSUNDEEP
postgresql
sandesh Jadhav
sandesh Jadhav

Reputation: 329

how to set max value for any column in Postgresql?

In my user's table, there is the column naming as daily_otp_count data type is an integer(daily_otp_count integer)

I want to set the max limit of that column to be 10, and at 12:00 am that value should be reset to 0.

Here's my table:

 Table "public.users"

             Column             |            Type             | Collation | Nullable |         Default
--------------------------------+-----------------------------+-----------+----------+-------------------------
  name_as_in_pan                | character varying(50)       |           |          |
 login_fail_timestamp           | timestamp without time zone |           |          |
 **daily_otp_count              | integer                     |           |          | 0**

Upvotes: 1

Views: 1516

Answers (1)

Insung Park
Insung Park

Reputation: 439

It may archive on server side logic, but if you want some db only solution. then you can apply DB Constraint and Procedure(If you want to store logic).

check these out

Example

Constraint

CREATE TABLE users (  -- or alter existing table
    ... 

    daily_otp_count numeric CONSTRAINT otp_max_limit CHECK (daily_otp_count <= 10)
);

Procedure

CREATE PROCEDURE update_user_otp_count_as_0()
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE public.users
  SET daily_otp_count = 0;
END;

CALL update_user_otp_count_as_0()

Even you don't use some backend server. You need to call this procedure from some scheduler like crontab.

You can use UPDATE statement without procedures. I didn't mention on this answer, i thought you already knew.

Upvotes: 1

Related Questions