PostgreSQL – How to Generate a Random Number within a Range

ByAdmin

You can use the RANDOM() function to generate a random floating-point number between 0 and 1 in PostgreSQL database. Here’s the basic syntax for using the RANDOM() function:

SELECT RANDOM();

This will return a random number in the range [0, 1), meaning it can be any decimal value between 0 (inclusive) and 1 (exclusive).

If you want to generate random integers within a specific range, you can use the following formula:

SELECT floor(random() * (max - min + 1) + min);

Here

  • random() generates a random floating-point number in the range [0, 1).
  • (max - min + 1) represents the range of integers you want.
  • floor() is used to round down the floating-point number to the nearest integer.
  • min is the lower bound of the range, and max is the upper bound.

For example, if you want to generate a random integer between 1 and 100 (inclusive), you can use the following SQL query:

SELECT floor(random() * (100 - 1 + 1) + 1);

This will give you a random integer between 1 and 100.

Similar Posts

PostgreSQL – How to Change Role within a Database Session

ByAdmin

You can change the current role within a database session in PostgreSQL database using SET ROLE statement . Changing the role allows you to temporarily assume the permissions and privileges associated with a different role, which can be useful for specific tasks or actions that require different access rights. This can be particularly helpful in…

PostgreSQL – How to Get Month Name From a Date

ByAdmin

To get the month name from a date in PostgreSQL, you can use the TO_CHAR function with the appropriate format pattern. Here’s an example: SELECT TO_CHAR(your_date_column, ‘Month’) AS month_name FROM your_table; Replace your_date_column with the name of your date column and your_table with the name of your table. In this query, TO_CHAR is used to…

PostgreSQL – How to Move a Table from One Schema to Another

ByAdmin

To move a table from one schema to another in PostgreSQL database, you can use the ALTER TABLE statement with the SET SCHEMA clause. Here’s the basic syntax: ALTER TABLE old_schema.table_name SET SCHEMA new_schema; where old_schema is the name of the current schema where the table resides. table_name is the name of the table you…

PostgreSQL – How to Get Current and Next Values of Sequence

ByAdmin

You can use Sequences to generate unique integer values, often used as auto-incrementing primary keys in tables. You can obtain the current value and the next value of a sequence using the currval and nextval functions, respectively. These functions are typically used within SQL statements or in PL/pgSQL code. Current Value (currval): To get the…

PostgreSQL – How to Extract Date from Timestamp

ByAdmin

You can extract date from a timestamp using various functions depending on the specific part of the date you want to extract. Here are some commonly used functions for extracting different parts of a date from a timestamp: Extracting Date Only (Year, Month, and Day):To extract only the date (year, month, and day) from a…