Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Examples with Numeric Types

CREATE TABLE Statement

The following CREATE TABLE statement demonstrates the declaration of different numeric data types:

Copy
create table film ( film_id integer, language_id smallint, original_language_id smallint, rental_duration smallint default 3, rental_rate numeric(4,2) default 4.99, length smallint, replacement_cost real default 25.00);

Attempt to Insert an Integer That is Out of Range

The following example attempts to insert the value 33000 into a SMALLINT column.

Copy
insert into film(language_id) values(33000);

The range for SMALLINT is -32768 to +32767, so Amazon Redshift returns an error.

Copy
An error occurred when executing the SQL command: insert into film(language_id) values(33000) ERROR: smallint out of range [SQL State=22003]

Insert a Decimal Value into an Integer Column

The following example inserts the a decimal value into an INT column.

Copy
insert into film(language_id) values(1.5);

This value is inserted but rounded up to the integer value 2.

Insert a Decimal That Succeeds Because Its Scale Is Rounded

The following example inserts a decimal value that has higher precision that the column.

Copy
insert into film(rental_rate) values(35.512);

In this case, the value 35.51 is inserted into the column.

Attempt to Insert a Decimal Value That Is Out of Range

In this case, the value 350.10 is out of range. The number of digits for values in DECIMAL columns is equal to the column's precision minus its scale (4 minus 2 for the RENTAL_RATE column). In other words, the allowed range for a DECIMAL(4,2) column is -99.99 through 99.99.

Copy
insert into film(rental_rate) values (350.10); ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^2 for field with precision 4, scale 2.

Insert Variable-Precision Values into a REAL Column

The following example inserts variable-precision values into a REAL column.

Copy
insert into film(replacement_cost) values(1999.99); insert into film(replacement_cost) values(19999.99); select replacement_cost from film; replacement_cost ------------------ 20000 1999.99 ...

The value 19999.99 is converted to 20000 to meet the 6-digit precision requirement for the column. The value 1999.99 is loaded as is.