PostgreSQL Data Types

In this chapter, we will discuss PostgreSQL data types, which are set for each field when we create a table.

The 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 different data types, and we will explain them in detail below.

Numeric types

Numeric types are composed of 2 Bytes,4 Bytes or 8 Integers in bytes and 4 Bytes or 8 Floating-point numbers in bytes and optional precision decimal numbers composed of bytes or

The following table lists the available numeric types.

Currency types

The money type stores currency amounts with fixed decimal precision.

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

Character types

The following table lists the character types supported by PostgreSQL:

Date/Time types

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

Boolean type

PostgreSQL supports the standard boolean data type.

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

Enumeration types

Enumeration types are a data type that is an ordered collection of static and values.

Enumeration types in PostgreSQL are similar to enum types in C language.

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

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

Create days of the week as shown below:

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

Like other types, once created, enumeration 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 type

Geometric data types represent two-dimensional plane objects.

The following table lists the geometric types supported by PostgreSQL.

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

network address type

PostgreSQL provides data types for storing IPv4 data type, IPv6 data type, MAC address

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

When sorting inet or cidr data types, IPv4 The address is always placed after IPv6 including those encapsulated or mapped on IPv address6 IPv address in the address4 address, for example::10.2.3.4 or ::ffff:10.4.3.2.

Bit string type

Bit strings are a sequence of 1 and the string of 0s.

bit type data must match the length n accurately, trying to store shorter or longer data is incorrect. bit varying type data is the variable length type with a maximum length of n; longer strings will be rejected. Writing a bit without length is equivalent to bit(1) means that there is no length limit for bit varying.

Text search type

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

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

UUID type

uuid data type is used to store RFC 4122, ISO/IEF 9834-8:2005 as well as the universally unique identifier (UUID) defined by related standards. (Some systems consider this data type as a globally unique identifier, or GUID.) This identifier is an algorithmically generated 128 bits of identifier, making it impossible for the identifier to be the same as that generated by other means in modules using 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.

UUID is written as a sequence of lowercase hexadecimal digits, divided into several groups, especially a group of8bits of digits+3sets4bits of digits+a set of12bits of digits, totaling 32 digits represent 128 bits, an example of a UUID instance of this standard is as follows:

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

XML type

xml data types can be used to store XML data. The advantage of storing XML data in text type is that it can check the input value for structural soundness, and also supports function type safety checks. To use this data type, the configure must be used at compile time. --with-libxml.

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

Create XML value

Use the function xmlparse: to generate 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 type

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.

There are also related functions to handle JSON data:

Array type

PostgreSQL allows field definitions to be variable-length multidimensional arrays.

Array types can be any basic type or user-defined type, enumeration type, or composite type.

Declare array

When creating a table, we can declare arrays in the following manner:

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 type array.

We can also use the "ARRAY" keyword as follows:

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

Insert values

The values are inserted using curly braces {}, elements are 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"}}');

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 index numbers of the array are written within square brackets.

Modify array

We can modify the values of an array:

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

Or use ARRAY constructor syntax:

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

Retrieval from array

To search for a value 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 all elements in an array that are equal to 10The rows of 000:

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;

Composite types

Composite types represent the structure of a row or a record; they are actually just a list of field names and their data types. PostgreSQL allows the use of composite types just like simple data types. For example, a field in a table can be declared as a composite type.

Declaration of composite types

The following are two simple examples of defining composite types:

CREATE TYPE complex AS (
　　　　double precision
　　　　i double precision
);
CREATE TYPE inventory_item AS (
　　　　name text,
　　　　supplier_id integer,
　　　　price numeric
);

The syntax is similar to CREATE TABLE, but here you can only declare field names and types.

Define the type, and we can use it to create a table:

CREATE TABLE on_hand (
　　　　item inventory_item,
　　　　count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice',　42,　1.99),　1000);

Input of composite type values

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

The general format of composite type constants is as follows:

' ( val1　, val2　, ... )'

An example is:

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

Accessing composite types

To access a domain of a composite type field, we write a dot and the name of the domain, which is very similar to 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 confusion by the parser. For example, you may need to select some subdomains from the on_hand instance 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 as follows:

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

Or if you also need to use table names (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 round bracket object is correctly parsed as a reference to the item field, and then subdomains can be selected from it.

Range type

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

For example, the timestamp range may be used to represent the time range a meeting room is booked.

The built-in range types in PostgreSQL include:

• int4range —integer range

• int8range —bigint range

• numrange —numeric range

• tsrange —timestamp without time zone range

• tstzrange —timestamp with time zone range

• daterange —date range

In addition, you can define your own range type.

CREATE　TABLE　reservation　(room　int,　during　tsrange);
INSERT　INTO　reservation　VALUES
　　　　(1108,　'[2010-01-01　14:30,　2010-01-01　15:30)');
--　Contain
SELECT　int4range(10,　20)　@>　3;
--　Overlap
SELECT　numrange(11.1,　22.2)　&&　numrange(20.0,　30.0);
--　Extract the upper bound
SELECT　upper(int8range(15,　25));
--　Calculate intersection
SELECT　int4range(10,　20)　*　int4range(15,　25);
--　Whether the range is empty
SELECT　isempty(numrange(1,　5));

The input of 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 boundaries are inclusive or exclusive. Note that the final format is empty, representing an empty range (a range that does not contain any values).

--　including3excluding7and including all points between the two
SELECT　'[3,7)'::int4range;
--　excluding3and7but including all points between the two
SELECT　'(3,7)'::int4range;
--　including a single value only4
SELECT　'[4,4]'::int4range;
--　excluding dots (which are standardized as 'empty')
SELECT　'[4,4)'::int4range;

Object Identifier Type

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

At the same time, the system will not add an OID system field to the tables created by the user (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.

Pseudo types

The PostgreSQL type system includes a series of entries with special purposes, which are called pseudo types according to categories. Pseudo types cannot be used as field data types, but they can be used to declare the parameter or result type of a function. Pseudo types are very useful when a function is not simply accepting and returning some SQL data type.

The following table lists all the pseudo types: