HOPEX Database Builder (Web) : PostgreSql 9.3: Syntax supported by HOPEX : Creating Datatype Packages
   
Creating Datatype Packages
 
PostgreSQL datatypes
PostgreSQL datatypes
A complete description of datatypes is accessible from the following link:
http://www.postgresql.org/docs/9.3/static/sql-commands.html
 
• Complete list
The following sections provide additional information.
Name
Aliases
Description
bigint
int8
signed eight-byte integer
bigserial
serial8
autoincrementing eight-byte integer
bit [ (n) ]
 
fixed-length bit string
bit varying [ (n) ]
varbit
variable-length bit string
boolean
bool
logical Boolean (true/false)
box
 
rectangular box on a plane
bytea
 
binary data ("byte array")
character [ (n) ]
char [ (n) ]
fixed-length character string
character varying [ (n) ]
varchar [ (n) ]
variable-length character string
cidr
 
IPv4 or IPv6 network address
circle
 
circle on a plane
date
 
calendar date (year, month, day)
double precision
float8
double precision floating-point number (8 bytes)
inet
 
IPv4 or IPv6 host address
integer
int, int4
signed four-byte integer
interval [ fields ] [ (p) ]
 
time span
json
 
JSON data
line
 
infinite line on a plane
lseg
 
line segment on a plane
macaddr
 
MAC (Media Access Control) address
money
 
currency amount
numeric [ (p, s) ]
decimal [ (p, s) ]
exact numeric of selectable precision
path
 
geometric path on a plane
point
 
geometric point on a plane
polygon
 
closed geometric path on a plane
real
float4
single precision floating-point number (4 bytes)
smallint
int2
signed two-byte integer
smallserial
serial2
autoincrementing two-byte integer
serial
serial4
autoincrementing four-byte integer
text
 
variable-length character string
time [ (p) ] [ without time zone ]
 
time of day (no time zone)
time [ (p) ] with time zone
timetz
time of day, including time zone
timestamp [ (p) ] [ without time zone ]
 
date and time (no time zone)
timestamp [ (p) ] with time zone
timestamptz
date and time, including time zone
tsquery
 
text search query
tsvector
 
text search document
txid_snapshot
 
user-level transaction ID snapshot
uuid
 
universally unique identifier
xml
 
XML data
 
• Numeric types
CREATE TABLE example_numeric_type
(
col_bigint bigint,
col_decimal1 decimal,
col_decimal2 decimal(8),
col_decimal3 decimal(8,2),
col_double_precision double precision,
col_integer integer,
col_numeric1 numeric,
col_numeric2 numeric(8),
col_numeric3 numeric(8,2),
col_real real,
col_serial serial,
col_bigserial bigserial,
col_smallserial smallserial,
col_smallint smallint
);
• Monetary types
CREATE TABLE example_monetary_type
(
col_money money
)
• Character strings
CREATE TABLE example_character_type
(
col_char1 char,
col_char2 char(12),
col_varchar1 varchar,
col_varchar2 varchar(15),
col_text text
)
• Binary types
CREATE TABLE example_binary_type
(
col_bytea bytea
)
• Time types
 
CREATE TABLE example_datetime_type
(
col_date date,
col_time1 time,
col_time2 time(6),
col_time3 time with time zone,
col_time4 time(6) with time zone,
col_timestamp1 timestamp,
col_timestamp2 timestamp(6),
col_timestamp3 timestamp with time zone,
col_timestamp4 timestamp(6) with time zone,
col_interval1 interval,
col_interval2 interval(6),
col_interval3 interval day,
col_interval4 interval day(6)
)
• Boolean types
CREATE TABLE example_boolean_type
(
col_boolean boolean
)
• Geometric types
CREATE TABLE example_geometric_type
(
col_point point,
col_line line,
col_lseg lseg,
col_box box,
col_path path,
col_polygon polygon,
col_circle circle
)
• Network types
CREATE TABLE example_network_type
(
col_cidr cidr,
col_inet inet,
col_macaddr macaddr
)
• Type : Bit String
Bit strings are strings of 1's and 0's. They can be used to store or visualize bit masks. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer. bit type data must match the length n exactly; it is an error to attempt to store shorter or longer bit strings. bit varying data is of variable length up to the maximum length n; longer strings will be rejected. Writing bit without a length is equivalent to bit(1), while bit varying without a length specification means unlimited length.
 
CREATE TABLE example_bit_type
(
col_bit1 bit,
col_bit2 bit(3),
col_bit_varying1 bit varying,
col_bit_varying2 bit varying(5)
)
• Type : Other
 
CREATE TABLE example_other_type
(
col_json json,
col_uuid uuid,
col_xml xml
)