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

PostgreSQL – How to Get the First Day of Month From a Date

ByAdmin

To get the first day of the month from a date in PostgreSQL, you can use the DATE_TRUNC function. Here’s an example: SELECT DATE_TRUNC(‘month’, your_date_column) AS first_day_of_month FROM your_table; where your_date_column is the name of your date column and your_table is the name of your table. This query will return the first day of the…

PostgreSQL – How to Convert String to Date

ByAdmin

You can convert a string to a date using the TO_DATE function in PostgreSQL. This function allows you to specify the input string, the format of the string, and it will return a date accordingly. The general syntax of the TO_DATE function is as follows: TO_DATE(input_string, format_string) Here’s an example of using SELECT TO_DATE(‘2023-08-31’, ‘YYYY-MM-DD’);…