PostgreSQL Data Types

In this chapter, we will discuss PostgreSQL data types. Data types are what we set for each field when creating a table.

Benefits of setting data types:

PostgreSQL provides a rich set of data types. Users can use the CREATE TYPE command to create new data types in the database. PostgreSQL has many data types, and we will explain them in detail below.

Numeric Types

Numeric types consist of two-, four-, or eight-byte integers, four- or eight-byte floating-point numbers, and selectable-precision decimals.

The following table lists the available numeric types.

Name	Storage Size	Description	Range
smallint	2 bytes	Small-range integer	-32768 to +32767
integer	4 bytes	Commonly used integer	-2147483648 to +2147483647
bigint	8 bytes	Large-range integer	-9223372036854775808 to +9223372036854775807
decimal	Variable length	User-specified precision, exact	131072 digits before the decimal point; 16383 digits after the decimal point
numeric	Variable length	User-specified precision, exact	131072 digits before the decimal point; 16383 digits after the decimal point
real	4 bytes	Variable precision, inexact	6 decimal digits precision
double precision	8 bytes	Variable precision, inexact	15 decimal digits precision
smallserial	2 bytes	Small autoincrementing integer	1 to 32767
serial	4 bytes	Autoincrementing integer	1 to 2147483647
bigserial	8 bytes	Large autoincrementing integer	1 to 9223372036854775807

Monetary Types

The money type stores monetary amounts with a fixed fractional precision.

Values of the numeric, int, and bigint types can be converted to money. Using floating-point numbers to handle monetary types is not recommended, because of the possibility of rounding errors.

Name	Storage Size	Description	Range
money	8 bytes	Currency amount	-92233720368547758.08 to +92233720368547758.07

Character Types

The following table lists the character types supported by PostgreSQL:

No.	Name & Description
1	character varying(n), varchar(n) Variable-length with limit
2	character(n), char(n) Fixed-length, blank-padded
3	text Variable-length, no length limit

No.

Name & Description

character varying(n), varchar(n)

Variable-length with limit

character(n), char(n)

Fixed-length, blank-padded

text

Variable-length, no length limit

Date/Time Types

The following table lists the date and time types supported by PostgreSQL.

Name	Storage Size	Description	Lowest value	Highest value	Resolution
timestamp [ (p) ] [ without time zone ]	8 bytes	Date and time (without time zone)	4713 BC	294276 AD	1 millisecond / 14 digits
timestamp [ (p) ] with time zone	8 bytes	Date and time, with time zone	4713 BC	294276 AD	1 millisecond / 14 digits
date	4 bytes	Date only	4713 BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	Time of day only	00:00:00	24:00:00	1 millisecond / 14 digits
time [ (p) ] with time zone	12 bytes	Time of day only, with time zone	00:00:00+1459	24:00:00-1459	1 millisecond / 14 digits
interval [ fields ] [ (p) ]	12 bytes	Time interval	-178000000 years	178000000 years	1 millisecond / 14 digits

Boolean Types

PostgreSQL supports the standard boolean data type.

The boolean type has two states, "true" (true) or "false" (false), and a third "unknown" (unknown) state, represented by NULL.

Name	Storage Format	Description
boolean	1 byte	true/false

Enumerated Types

An enumerated type is a data type that contains an ordered set of static values.

Enumerated types in PostgreSQL are similar to the enum types in C language.

Unlike other types, enumerated types need to be created using the CREATE TYPE command.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Create the days of a week, as shown below:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Like other types, once created, enumerated types can be used in table and function definitions.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

Geometric Types

Geometric data types represent two-dimensional planar objects.

The following table lists the geometric types supported by PostgreSQL.

The most basic type: point. It is the foundation of the other types.

Name	Storage Size	Description	Representation
point	16 bytes	Point in the plane	(x,y)
line	32 bytes	(Infinite) line (not fully implemented)	((x1,y1),(x2,y2))
lseg	32 bytes	(Finite) line segment	((x1,y1),(x2,y2))
box	32 bytes	Rectangle	((x1,y1),(x2,y2))
path	16+16n bytes	Closed path (similar to polygon)	((x1,y1),...)
path	16+16n bytes	Open path	[(x1,y1),...]
polygon	40+16n bytes	Polygon (similar to closed path)	((x1,y1),...)
circle	24 bytes	circle	<(x,y),r> (center and radius)

Network Address Types

PostgreSQL provides data types for storing IPv4, IPv6, and MAC addresses.

Storing network addresses with these data types is better than using plain text types, because these types provide input error checking and special operations and functions.

Name	Storage Size	Description
cidr	7 or 19 bytes	IPv4 or IPv6 network
inet	7 or 19 bytes	IPv4 or IPv6 host and network
macaddr	6 bytes	MAC address

When sorting inet or cidr data types, IPv4 addresses are always sorted before IPv6 addresses, including IPv4 addresses that are encapsulated or mapped within IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2.

Bit String Types

A bit string is a string of 1s and 0s. They can be used to store and visualize bit masks. We have two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.

Data of the bit type must exactly match length n; attempting to store shorter or longer data is an error. Data of the bit varying type is a variable-length type with a maximum length of n; longer strings will be rejected. Writing a bit without a length is equivalent to bit(1), and bit varying without a length means no length limit.

Text Search Types

Full-text search is the retrieval of documents from a collection of natural language documents that match a query.

PostgreSQL provides two data types to support full-text search:

Number	Name & Description
1	tsvector The value of tsvector is a sorted list of distinct lexemes, that is, normalized forms of different variants of the same word.
2	tsquery tsquery stores the lexemes used for searching, and combines them using the Boolean operators & (AND), \| (OR), and ! (NOT); parentheses are used to enforce grouping of operators.

Number

Name & Description

tsvector

The value of tsvector is a sorted list of distinct lexemes, that is, normalized forms of different variants of the same word.

tsquery

tsquery stores the lexemes used for searching, and combines them using the Boolean operators & (AND), | (OR), and ! (NOT); parentheses are used to enforce grouping of operators.

UUID Type

The uuid data type is used to store universally unique identifiers (UUID) as defined by RFC 4122, ISO/IEF 9834-8:2005, and related standards. (Some systems refer to this data type as a globally unique identifier, or GUID.) This identifier is a 128-bit identifier produced by an algorithm, making it impossible for it to be the same as identifiers produced by other means among modules known to use the same algorithm. Therefore, for distributed systems, this identifier provides a better uniqueness guarantee than sequences, because sequences can only guarantee uniqueness in a single database.

UUIDs are written as a sequence of lowercase hexadecimal digits, divided into groups by hyphens, specifically a group of 8 digits plus 3 groups of 4 digits plus a group of 12 digits, totaling 32 digits representing 128 bits. An example of such a standard UUID is as follows:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML Type

The xml data type can be used to store XML data. Its advantage over storing XML data in the text type is that it can check input values for well-formedness, and it also supports functions for type-safe checking of them. To use this data type, you must use it at compile time.configure --with-libxml。

xml can store well-formed "documents" defined by the XML standard, as well as by the XML standard'sXMLDecl? contentdefined "content" fragments; roughly, this means that content fragments can have multiple top-level elements or character nodes. The xml value IS DOCUMENT expression can be used to determine whether a particular xml value is a complete document or a content fragment.

Creating XML Values

Use the function xmlparse: to produce xml type values from character data:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON Types

The json data type can be used to store JSON (JavaScript Object Notation) data. Such data can also be stored as text, but the json data type is more advantageous for checking that each stored value is a valid JSON value.

In addition, there are related functions for processing json data:

Example	Example Result
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{"f1":1,"f2":"foo"}

Array Types

PostgreSQL allows fields to be defined as variable-length multidimensional arrays.

Array types can be any base type or user-defined type, enum type, or composite type.

Declaring Arrays

When creating a table, we can declare arrays as follows:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter is a one-dimensional integer array, and schedule is a two-dimensional text array.

We can also use the "ARRAY" keyword, as shown below:

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

Inserting Values

To insert values, use curly braces {}, with elements separated by commas inside {}:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Accessing Arrays

Now we can run some queries on this table.

First, we demonstrate how to access a single element of an array. This query retrieves the names of employees whose salaries changed in the second quarter:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

The subscript numbers of an array are written inside square brackets.

Modifying Arrays

We can modify the values of an array:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

Or use the ARRAY constructor syntax:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Searching in Arrays

To search for a value in an array, you must check every value of the array.

For example:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Alternatively, you can use the following statement to find rows where all element values in the array are equal to 10000:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Or, you can use the generate_subscripts function. For example:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Composite Types

A composite type represents the structure of a row or a record; it is actually just a list of field names and their data types. PostgreSQL allows composite types to be used in the same way as simple data types. For example, a field of a table can be declared as a composite type.

Declaring Composite Types

Below are two simple examples of defining composite types:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

The syntax is similar to CREATE TABLE, except that here only field names and types can be declared.

Once the types are defined, we can use them to create tables:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Composite Type Value Input

To write a composite type value as a text constant, enclose the field values in parentheses and separate them with commas. You may put double quotes around any field value; if the value itself contains commas or parentheses, you must enclose it in double quotes.

The general format of a composite type constant is as follows:

'( val1 , val2 , ... )'

An example is:

'("fuzzy dice",42,1.99)'

Accessing Composite Types

To access a field of a composite type column, we write a dot and the field name, much like selecting a field from a table name. In fact, because it is so similar to selecting a field from a table name, we often need to use parentheses to avoid confusing the parser. For example, you might need to select some subfields from the on_hand example table, like this:

SELECT item.name FROM on_hand WHERE item.price > 9.99;

This will not work, because according to SQL syntax, item is selected from a table name, not a field name. You must write it like this:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Or if you also need to use the table name (for example, in a multi-table query), then write it like this:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Now the parenthesized object is correctly parsed as a reference to the item field, and then you can select subfields from it.

Range Types

Range data types represent a certain range of values of a certain element type.

For example, a timestamp range might be used to represent the time range during which a meeting room is reserved.

The built-in range types in PostgreSQL are:

int4range — range of integer
int8range — range of bigint
numrange — range of numeric
tsrange — range of timestamp without time zone
tstzrange — range of timestamp with time zone
daterange — range of date

In addition, you can define your own range types.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

Input for range values must follow the following format:

(lower bound, upper bound)
(lower bound, upper bound]
[lower bound, upper bound)
[lower bound, upper bound]
empty

Parentheses or square brackets indicate whether the lower and upper bounds are exclusive or inclusive. Note that the last format is empty, representing an empty range (a range that contains no values).

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

Object Identifier Types

PostgreSQL internally uses object identifiers (OIDs) as primary keys for various system tables.

At the same time, the system will not add an OID system field to user-created tables (unless WITH OIDS is declared when creating the table, or the configuration parameter default_with_oids is set to on). The oid type represents an object identifier. In addition, oid has several aliases: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, and regdictionary.

Name	Reference	Description	Example
oid	any	numeric object identifier	564182
regproc	pg_proc	function name	sum
regprocedure	pg_proc	function with argument types	sum(int4)
regoper	pg_operator	operator name	+
regoperator	pg_operator	operator with argument types	*(integer,integer) or -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	data type name	integer
regconfig	pg_ts_config	text search configuration	english
regdictionary	pg_ts_dict	text search dictionary	simple

Pseudo-Types

The PostgreSQL type system contains a number of special-purpose entries that are collectively called pseudo-types. Pseudo-types cannot be used as data types of fields, but they can be used to declare the argument or result type of a function. Pseudo-types are useful when a function does not simply accept and return a certain SQL data type.

The following table lists all pseudo-types:

Name	Description
any	Indicates that a function accepts any input data type.
anyelement	Indicates that a function accepts any data type.
anyarray	Indicates that a function accepts any array data type.
anynonarray	Indicates that a function accepts any non-array data type.
anyenum	Indicates that a function accepts any enum data type.
anyrange	Indicates that a function accepts any range data type.
cstring	Indicates that a function accepts or returns a null-terminated C string.
internal	Indicates that a function accepts or returns a server-internal data type.
language_handler	A procedural language call handler is declared to return language_handler.
fdw_handler	A foreign data wrapper is declared to return fdw_handler.
record	Identifies a function as returning an unspecified row type.
trigger	A trigger function is declared to return trigger.
void	Indicates that a function does not return a value.
opaque	An obsolete type, previously used for all of the above purposes.

For more information, see:PostgreSQL Data Types

Other Extensions