5.6.1. Numerical type ¶

Numeric types consist of 2-byte, 4-byte, or 8-byte integers, 4-or 8-byte floating-point numbers, and optional precision decimal numbers.

The following table lists the available numeric types.

5.6.2. Currency type ¶

The money type stores monetary amounts with fixed decimal precision.

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

5.6.3. Character type ¶

The following table lists the character types supported by PostgreSQL:

5.6.4. Date / time type ¶

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

5.6.5. Boolean type ¶

PostgreSQL supports standard boolean data types.

Boolean has two states: “true” (true) or “false” (false), and the third “unknown” state, represented by NULL.

5.6.6. Enumerated type ¶

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

The enumeration types in PostgtesSQL are similar to those in C enum Type.

Unlike other types, enumerated types need to use the CREATE TYPE Command to create.

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

Create several days of the week, as follows:

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

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

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)

5.6.7. Geometric type ¶

The geometric data type represents a two-dimensional planar object.

The following table lists the geometry types supported by PostgreSQL.

The most basic type: points. It is the basis of other types.

5.6.8. Network address type ¶

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

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

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

5.6.9. Bit string type ¶

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

Data of type bit must accurately match the length n, and it is wrong to try to store shorter or longer data. Bit varying type data is the variable length type with the longest n; longer strings are rejected. Writing a bit without length is equivalent to bit (1), and bit varying without length means no length limit.

5.6.10. Text search type ¶

Full-text retrieval is to find those that match a query through a collection of natural language documents.

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

5.6.11. UUID Typ ¶

The uuid data type is used to store the universal unique identifiers (UUID) defined by RFC 4122 ISO IEF 9834-8 UUID and related standards. Some systems consider this data type to be a globally unique identifier, or GUID. This identifier is a 128-bit identifier generated by the algorithm, making it impossible for it to be the same as the identifier generated in other ways in a module known to use the same algorithm Therefore, for distributed systems, this identifier can provide a better guarantee of uniqueness than sequences, because sequences can only be unique in a single database.

UUID is written as a sequence of lowercase hexadecimal numbers, divided into several groups of characters, especially a group of 8 digits + 3 groups of 4 digits + a group of 12 digits, with a total of 32 digits representing 128. an example of such a standard UUID is as follows:

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

5.6.12. XML Typ ¶

The xml data type can be used to store XML data. The advantage of saving XML data in a text type is that it can check input values for good structure, and it also supports functions to check its type safety. To use this data type, you must use configure– with-libxml at compile time.

Xml can store well-formed “documents” defined by the XML standard, as well as those defined by the XML standard XMLDecl? content A defined “content” fragment, which roughly means that a content fragment can have multiple top-level elements or character nodes. Xmlvalue IS DOCUMENT expressions can be used to determine whether a particular xml value is a complete file or a content fragment.

5.6.13. Create an XML value ¶

Use the function xmlparse: to generate values of type xml 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>')

5.6.14. JSON Typ ¶

The json data type can be used to store JSON (JavaScript Object Notation) data, and such data can also be stored as text, but the json data type is more useful to check that each stored value is an available JSON value.

In addition, there are related functions to process json data:

5.6.15. Array type ¶

PostgreSQL allows you to define fields as multi-dimensional arrays of variable length.

The array type can be any basic type or user-defined type, enumerated type, or compound type.

5.6.16. Declare array ¶

When we create a table, we can declare an array as follows:

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

Pay_by_quarter is an one-dimensional array of integers and schedule is a two-dimensional array of text types.

We can also use the “ARRAY” keyword, as follows:

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

5.6.17. Insert value ¶

Insert values with curly braces {}, and elements are separated by commas:

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"}}');

5.6.18. Access array ¶

Now we can run some queries on this table.

First, we demonstrate how to access an 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 the array are written in square brackets.

5.6.19. Modify array ¶

We can modify the values of the array:

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

或者使用 ARRAY 构造器语法：

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

5.6.20. Search in array ¶

To search for values in an array, you must check each value in 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;

In addition, you can use the following statement to find rows in the array where all elements have a value equal to 10000:

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

Alternatively, 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;

5.6.21. Compound type ¶

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

5.6.22. Declare compound types ¶

Here 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 only field names and types can be declared here.

Once the type is defined, we can use it to create the table:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

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

5.6.23. Compound type value input ¶

To write compound values as text constants, surround the field values in parentheses and separate them with commas. You can put double quotation marks around any field value, and if the value itself contains commas or parentheses, you must enclose it in double quotes.

The general format of compound type constants is as follows:

'( val1 , val2 , ... )'

One example is:

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

5.6.24. Access compound type ¶

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

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

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

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

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

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

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

5.6.25. Range Typ ¶

The range data type represents the value of an element type within a certain range.

For example, the timestamp range may be used to represent a scheduled time range for a conference room.

The built-in range types of PostgreSQL are:

• Range of int4range-integer

• Range of int8range-bigint

• Range of numrange-numeric

• Range of tsrange-timestamp without time zone

• Range of tstzrange-timestamp with time zone

• Range of daterange-date

In addition, you can define your own scope type.

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));

The input of range values must follow the following format:

(下边界,上边界)
(下边界,上边界]
[下边界,上边界)
[下边界,上边界]
空

Parentheses or square brackets indicate whether the lower and upper boundaries are excluded or included. Note that the final format is empty, representing an empty range (a range with no values).

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

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

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

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

5.6.26. Object identifier type ¶

PostgreSQL internally uses the object identifier (OID) as the primary key for various system tables.

At the same time, the system does not add an OID system field to the user-created table (unless the WITH OIDS is declared 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.

5.6.27. Pseudo type ¶

The PostgreSQL type system contains a series of special-purpose entries that are called pseudo-types by category. A pseudo type cannot be used as a data type for a field, but it can be used to declare a function’s parameter or result type. Pseudo-types are useful in situations where a function does not simply accept and return a certain SQL data type.

The following table lists all pseudo types:

For more information, see: PostgreSQL 数据类型