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

Data Types

SQL is considered a strongly typed language . This means that any piece of data represented by PostgreSQL has an associated data type, even if it is not plainly obvious. A data value's type both defines and constrains the kinds of operations which may be performed on it.

Not only is every piece of data associated with a type, but types play a large part in the construction of tables. As stated in the Section called Introduction to Relational Databases ," tables are made up of one or more columns. These columns must, in addition to having a name, have a specific data type.

Note: While PostgreSQL provides a wide variety of built-in data types, you also have the option to add new data types to PostgreSQL using the CREATE TYPE command. See the reference entry on CREATE TYPE for more on this command.

Table 3-10 lists the data types officially supported by PostgreSQL, as well as any PostgreSQL recognized aliases (alternative names that are identical in connotation). There are many other internal (meaning they are no longer intended for normal use) or deprecated (outdated, and discouraged) data types available that are unlisted.

Additionally, while most of the data types implemented in PostgreSQL are directly derived from SQL standards, there are some actively maintained data types that are non-standard (such as the geometric and spacial types). Therefore, you will not always be able to find equivalent types on other SQL-capable database management systems.

Table 3-10. PostgreSQL supported data types

Category

Data type

Description

Standardization

Boolean and binary types

boolean, bool

A single true or false value.

SQL99

bit( n  )

An n  -length bit string (exactly n binary bits).

SQL92

bit varying( n  ), varbit( n  )

A variable n  -length bit string (up to n binary bits)

SQL92

Character types

character ( n  ), char( n  )

A fixed n  -length character string.

SQL89

character varying( n  ), varchar( n  )

A variable length character string of up to n characters.

SQL92

text

A variable length character string, of unlimited length.

PostgreSQL-specific

Numeric types

smallint, int2

A signed 2-byte integer.

SQL89

integer, int, int4

A signed, fixed-precision 4-byte number.

SQL92

bigint, int8

A signed 8-byte integer, up to 18 digits in length.

PostgreSQL-specific

real, float4

A 4-byte floating-point number.

SQL89

double precision, float8, float

An 8-byte floating-point number.

SQL89

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

An exact numeric type with arbitrary precision p , and scale s .

SQL99

money

A fixed precision, U.S.-style currency.

PostgreSQL-specific, deprecated.

serial

An auto-incrementing 4-byte integer.

PostgreSQL-specific

Date and time types

date

The calendar date (day, month and year).

SQL92

time

The time of day.

SQL92

time with time zone

The time of day, including time zone information.

SQL92

timestamp (includes time zone)

Both the date and time.

SQL92

interval

An arbitrarily specified length of time.

SQL92

Geometric types

box

A rectangular box in a 2D plane.

PostgreSQL-specific

line

An infinite line in a 2D plane.

PostgreSQL-specific

lseg

A finite line segment in a 2D plane.

PostgreSQL-specific

circle

A circle with center and radius.

PostgreSQL-specific

path

Open and closed geometric paths in a two-dimensional plane.

PostgreSQL-specific

point

geometric point in a 2D plane

PostgreSQL-specific

polygon

A closed geometric path in a 2D plane.

PostgreSQL-specific

Network types

cidr

An IP network specification.

PostgreSQL-specific

inet

A network IP address, with optional subnet bits.

PostgreSQL-specific

macaddr

A MAC address (e.g., an Ethernet card's hardware address).

PostgreSQL-specific

System types

oid

An object (row) identifier.

PostgreSQL-specific

xid

A transaction identifier.

PostgreSQL-specific

Remaining true to theme, the following sections on data types will describe in further detail each of the most widely used and practical types. This book will not go into detail on the non-standard and/or more esoteric types, such as the geometric, network and bitwise types. These sections include information on valid usage, storage considerations, input and output formats and general syntactic conventions. Before we go much further on specific data types there are a couple of topics worth discussing, including the NULL keyword.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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