Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Numeric Types

PostgreSQL's numeric types are used to represent both integers and decimal floating-point values. From a general perspective, PostgreSQL's supported numeric types consist of:

  • Two-, four-, and eight-byte integers

  • Four- and eight-byte floating-point numbers

  • Fixed precision decimals

PostgreSQL has support for special types which fall under the family of numeric types, including the deprecated money type, and the special serial construct.

Table 3-13. Numeric types overview

Data type

Storage

Range

bigint, int8

8 bytes

Whole integer values, –9,223,372,036,854,775,807 to +9,223,372,036,854,775,807

double precision, float8, float

8 bytes

Floating-point integer values, 15 significant digits, unlimited size (with limited precision)

integer, int, int4

4 bytes

Whole integer values, –2147483648 to +2147483647

numeric( p , s  ), decimal ( p , s  )

Variable

Whole or floating point integers defined as p total digits (including digits to the right of the decimal) with s digits to the right of the decimal point

real, float4

4 bytes

Floating-point integer values, six significant digits, unlimited size (with limited precision)

smallint, int2

2 bytes

Whole integers, –32768 to +32767

money

4 bytes

Floating-point integer values with a scale of two digits to the right of the decimal, —21474836.48 to +21474836.47

serial

4 bytes

Whole integers, 0 to 2147483647

As shown in Table 3-13, several of PostgreSQL's data types have aliases that are equivalent to their associated data types. This was done for ease of use, but at times it can be confusing, due to the fact that some of the aliases sound familiar. If you are not careful to understand what data type an alias you are using is associated with, you may accidentally reference the wrong data type. For example, in PostgreSQL the real and double precision data types represent numbers you may be more familiar to using a float variable in other languages; however, because they both have aliases that contain the word "float" (float and float8 link to double precision; float4 links to real). Problems may result if if you attempt to use the float alias, thinking it is linked to real, when in fact it is associated with double precision.

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.

Note: PostgreSQL will not always return an error if you violate the precision and scale of a numeric column.

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.

Example 3-22. Avoiding overflow errors

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.

Example 3-23. A numeric alternative to money

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.

Example 3-24. Using the serial data type

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

Example 3-25. Accomplishing the same goal manually

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

Caution with Implicit Sequences

Upon dropping a table, the implicit sequence created for the serial types are not automatically dropped. You must clean up after these types of sequences if you destroy a table which had a serial column, as shown in Example 3-24, with the DROP SEQUENCE command.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire