The numeric type

The numeric (also known as decimal) type is a specially designed numeric data type that can represent arbitrarily large and precise values within a fixed length that is given by the user. When you create a table with a column of type numeric, you may specify in parentheses two values: the precision and the scale .

The precision is the maximum number of digits that the numeric value may hold (including digits to the right of the decimal point), while the scale describes how many of those digits of precision are to be to the right of the decimal point. If left unspecified, the precision will default to 30 digits, and scale to 6 digits. The maximum precision (and, hence, the maximum scale) you can set this to is 1,000. Setting the precision to 1,000 would allow a maximum 1,000 digits, which should be fairly adequate for most needs.

Unlike the floating-point data types, you will receive an overflow error if you attempt to insert a number that is larger than the allotted precision range. Beside this limitation, you should be able to insert any number that fits within the provided precision and scale of the numeric type column.

For example, in a numeric(11,6) column, you may safely insert the value 9.999999 with two digits too many to the right of the decimal point (though the value is rounded up to 10.000000). However, an attempt to insert the value 99999.99999999 will fail, as shown in Example 3-22.

Problems that arise from trying to insert values that are two large can be avoided by using the trunc() numeric truncating function within an INSERT command to make sure a number is truncated to a size suitable for the column it is being inserted into. You must provide the length it should be truncated to, which means you'll have to be aware of the precisions you've previously specified. The use of trunc() is also illustrated within Example 3-22.

booktown=# 
INSERT INTO numbers VALUES (9.99999999);

INSERT 3390697 1
booktown=# 
SELECT * FROM numbers;

    number
--------------
    10.000000
(1 row)

booktown=# 
INSERT INTO numbers VALUES (99999.99999999);

ERROR:  overflow on numeric ABS(value) >= 10^5 for field with precision 11 scale 6
booktown=# 
INSERT INTO numbers VALUES (trunc(99999.99999999, 6));

INSERT 3390698 1
booktown=# 
SELECT * FROM numbers;

    number
--------------
    10.000000
 99999.999999
(2 rows)

booktown=# 
INSERT INTO numbers VALUES (trunc(9.99999999, 6));

INSERT 3390699 1
booktown=# 
SELECT * FROM numbers;

    number
--------------
    10.000000
 99999.999999
     9.999999
(3 rows)

The monetary type

The money type stores U.S.-style currency notation and plain numeric values. As of the writing of this book, the money type is deprecated, and is discouraged from being actively used. It is only presented here as it is still a functional data type, and may be in use on existing PostgreSQL systems.

The suggested alternative to the money type is the numeric type, with a scale of 2 to represent coin values, and a precision large enough to store the largest necessary monetary value (including two digits for the coin precision). Formatting similar to that of the money type can be achieved with the to_char() function, as shown in Example 3-23. This example demonstrates the text concatenation operator, and the ltrim() text formatting function, each described in Chapter 4.

booktown=# 
CREATE TABLE money_example (money_cash money, numeric_cash numeric(10,2));    

CREATE
booktown=# 
INSERT INTO money_example VALUES ('$12.24', 12.24);

INSERT 3391095 1
booktown=# 
SELECT * FROM money_example;

 money_cash | numeric_cash
------------+--------------
     $12.24 |        12.24
(1 row)

booktown=# 
SELECT money_cash, 

booktown-# 
       '$' || ltrim(to_char(numeric_cash, '9999.99')) 

booktown-# 
       AS numeric_cashified 

booktown-# 
       FROM money_example;

 money_cash | numeric_cashified
------------+-------------------
     $12.24 | $12.24
(1 row)

The serial type

The serial type is a non-standard but useful shortcut which allows you to easily create an identifier column within a table that contains a unique value for each row. The serial type literally combines the functionality of a 4-byte integer data type, an index, and a sequence. Example 3-24 shows the serial type being used to generate a unique identifier for each row in a table named auto_identifier. Example 3-25 shows the same thing being accomplished using an integer column, the nextval() function , and a sequence. As of the writing of this book, these two methods are functionally identical.

See Chapter 7 for more information on using sequences.

booktown=# 
CREATE TABLE auto_identified (id serial);

NOTICE:  CREATE TABLE will create implicit sequence 'auto_identified_id_seq'
for SERIAL column 'auto_identified.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key'
for table 'auto_identified'
CREATE

booktown=# 
CREATE SEQUENCE auto_identified_id_seq;

CREATE
booktown=# 
CREATE TABLE auto_identified 

booktown-# 
(id integer UNIQUE DEFAULT nextval('auto_identified_id_seq'));

NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key' for table 'auto_identified'
CREATE