What Is Numeric Data Type in Redshift?

The Numeric data type in Redshift is one of the most commonly used data types for storing numeric values with a specified precision and scale. It allows you to store both integers and decimal numbers in a highly efficient manner.

Understanding Numeric Data Type

The Numeric data type is used to store numbers that require a high level of precision. It allows you to define the total number of digits (precision) and the number of digits to the right of the decimal point (scale).

For example, if you define a numeric column with a precision of 10 and a scale of 2, you can store numbers like 1234567.89 or 12.34.

Creating a Numeric Column

To create a numeric column in Redshift, you need to specify the precision and scale when defining the column. Here’s an example:

CREATE TABLE sales (
   id INT,
   amount NUMERIC(10, 2)
);

In this example, we create a table called “sales” with two columns – “id” which is an integer column, and “amount” which is a numeric column with a precision of 10 and a scale of 2.

Performing Arithmetic Operations

Numeric data types in Redshift support basic arithmetic operations such as addition, subtraction, multiplication, and division. You can perform these operations directly on numeric columns or use them in SQL expressions.

SELECT amount * 1.1 AS increased_amount
FROM sales;

This query multiplies the “amount” column by 1.1 and returns the result as “increased_amount”.

Rounding Numbers

If you need to round numeric values to a specific decimal place, Redshift provides several functions like ROUND, CEILING, and FLOOR.

SELECT ROUND(amount, 2) AS rounded_amount
FROM sales;

This query rounds the “amount” column to two decimal places and returns the result as “rounded_amount”.

Aggregating Numeric Data

You can also perform various aggregate functions on numeric columns in Redshift. Some commonly used aggregate functions include SUM, AVG, MIN, MAX.

SELECT SUM(amount) AS total_sales
FROM sales;

This query calculates the sum of all values in the “amount” column and returns the result as “total_sales”.

Conclusion

The Numeric data type in Redshift is a powerful tool for storing numeric values with precision and scale. It allows you to perform arithmetic operations, round numbers, and aggregate data efficiently. By understanding how to use the Numeric data type effectively, you can make the most out of your Redshift database.