how to set max value for any column in Postgresql?

Question

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**

Answer 1

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

• Postgresql Constraint: https://www.postgresql.org/docs/current/ddl-constraints.html
• PostgreSQL Procedure: https://www.postgresql.org/docs/current/sql-createprocedure.html

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.

Answer 2

Here you go. I have created a variable that allows you to select that which column of the second dataframe you want to multiply with the numbers in the first dataframe.

arr1 = np.array(df1) # df1 to array

which_df2col_to_multiply = 0  # select col from df2
arr2 = np.array(df2)[:, which_df2col_to_multiply ] # selected col to array

print(np.transpose(arr2*arr1))  # result

This is the output:

[[1 2 3]
[4 4 4]
[12 8 4]]

Answer 3

Multiply the first df by the column of the second df

Assuming your column names are 0,1,2

df1.mul(df2[1],0)

Output

    0  1  2
0   1  2  3
1   4  4  4
2  12  8  4