PostgreSQL data type

PostgreSQL has a wide range of data types available. Users can use the CREATE type command to Add new data types to PostgreSQL.

Table 8-1 shows all the built-in common data types. Most of the optional names listed in the aliases column are names that are used internally by PostgreSQL for historical reasons . In addition, there are some internally used or deprecated types that can be used, but not listed here.

Table 8-1. Data type

name	aliases	Description
bigint	int8	Signed 8-byte integer
Bigserial	Serial8	Self-increment eight-byte integer
bit [(n)]		Fixed-length string
Bit varying [(n)]	Varbit	Variable-length string
Boolean	bool	Logical Boolean Amount (TRUE/False)
Box		A rectangle in a plane
Bytea		Binary data ("byte array")
character varying [(n)]	varchar [(n)]	Variable length string
character [(n)]	char [(n)]	Fixed length string
Cidr		IPV4 or IPV6 network address
Circle		A circle in a plane
Date		Calendar date (year, month, day)
Double precision	Float8	Double-precision floating-point numbers
inet		IPV4 or IPV6 network address
Integer	int,int4	Four-byte long signed integer
interval [(p)]		Time interval
Line		An infinitely long line in a plane
Lseg		Segments in a plane
Macaddr		MAC Address
Money		Currency Amount
numeric [(p, s)]	decimal [(p, s)]	Accurate numbers for optional accuracy
Path		Geometric paths in a plane
Point		Points in a plane
Polygon		Closed geometric paths in a plane
Real	Float4	Single-precision floating-point number
smallint	Int2	Signed Two-byte integer
Serial	Serial4	Self-increment four-byte integer
Text		Variable length string
Time [(P)] [without time zone]		A day's time
Time [(P)] with time zone	Timetz	Time of day, including time zone
timestamp [(p)] [without time zone]		Date and time
timestamp [(P)] with time zone	Timestamptz	Date and time

Compatibility: The following types (or that are spelled) are SQL-declared : bit,bit varying,boolean, char, Character,character varying,varchar,date, double precision, Integer, interval,numeric,decimal, real,smallint, time (including occasional and non-timezone), timestamp (including occasional zones and no time zones).

Each data type has an external representation that is determined by its input and output functions. Many of the built-in types have obvious formatting. However, many types are either specific to PostgreSQL, such as geometric paths, or may have several different formats, such as date and time types. Some of the input and output functions are irreversible. In other words, the output function output and the original input comparison may lose precision.

8.1. Numeric type

Numeric types consist of 2, 4, or 8-byte integers, and 4 or 8-byte floating-point numbers and optional precision decimals. Table 8-2 lists all available types.

Table 8-2. Numeric type

name	Storage Space	Description	Range
smallint	2 bytes	Small Range integer	32768 to +32767
Integer	4 bytes	Commonly used integers	2147483648 to +2147483647
bigint	8 bytes	Large range of integers	9223372036854775808 to 9223372036854775807
Decimal	Variable length	User declaration accuracy, accurate	Unlimited
Numeric	Variable length	User declaration accuracy, accurate	Unlimited
Real	4 bytes	Variable precision, imprecise	6-bit decimal digit precision
Double precision	8 bytes	Variable precision, imprecise	15-bit decimal digit precision
Serial	4 bytes	Self-increment integer	1 to +2147483647
Bigserial	8 bytes	Large range of self-increment integers	1 to 9223372036854775807

The syntax for numeric type constants is described in Section 4.1.2. numeric types correspond to a complete set of mathematical operators and functions. Please refer to Chapter 9 for related information. The following sections describe these types in detail.

8.1.1. Integer types

The types smallint,integers, and bigint store all the various ranges of numbers, that is, numbers with no decimal parts. Attempting to store values outside the range will result in an error.

The common type is integerbecause it provides the best balance between scope, storage space, and performance. smallintis generally used only when disk space is tight. Use bigintonly when the range of integers is not enough, because the former is much faster.

The bigint type may not be working correctly on all platforms because it relies on the compiler's support for eight-byte integers. On machines with no such support, the bigint function is the same as an integer (but still occupies eight bytes of storage). However, we do not yet know any platform with such a situation.

SQL only declares integer type integers (or int) and smallint. Type bigint, and the type name int2,int4, and int8 are all extensions and are also used in many other SQL database systems.

8.1.2. Arbitrary precision values

Type numeric can store numbers with up to 1000 digits of precision and calculate them accurately. We specifically recommend that you use it for currency amounts and other requirements to calculate the exact amount. However, the arithmetic operations on thenumeric type are much slower than the integer type or the floating-point number types described in our next section.

In the subsequent content, we used the following terminology: the proportion of a numeric is the number of digits of the fractional part to the right of the decimal point, and the precision of the numeric Is the number of all data bits in the entire number, that is, the number of data on both sides of the decimal point. So the accuracy of the number 23.5141 is 6 and the ratio is 4. You can assume that the proportion of integers is zero.

The maximum precision and maximum scale of the numeric field is configurable. To declare a field of type numeric , you can use the following syntax

NUMERIC (precisionscale)

The precision must be positive, and the scale can be zero or positive. Other than that

NUMERIC (precision)

0 is selected as a scale. Without any precision or proportional declaration

NUMERIC

Creates a numeric value that can store an arbitrary precision and scale up to the maximum precision, and a field of this type will not convert the input value to any particular scale, and the numeric field with the proportional declaration will convert the input to that scale. (the SQL standard requires a default scale of 0.) Which translates into integer precision. We find it a little useless to do so. If you care about portability, you'd better always make a clear statement about accuracy and proportions. )

If the scale of a value to be stored is higher than the scale of the field declaration, the system will try rounding (rounding) the number to the specified decimal place. Then, if the number of data bits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is thrown.

Numeric data values are physically stored without any leading or suffix zeros. Therefore, the precision and scale declared on the field are the maximum, not the fixed allocation. (In this respect, thenumeric type is more similar to varchar (n), rather than char (n). The actual storage is two bytes per four decimal bits, and then adds eight bytes of extra overhead to the entire data.

In addition to the normal numeric values, thenumeric type allows a special value of NaN, which means "not a number." Any operation above the Nan generates another nan. If you write these values as a constant in a SQL command, you must enclose them in single quotes, such as UPDATE table SET x = ' NaN '. As input, the string NaN is treated as case insensitive.

The type decimal and numeric are equivalent. Both of these types are SQL standards.

8.1.3. Floating-point number types

Data types Real and double precision are inaccurate, variable-precision numeric types. In fact, these types are general implementations of the IEEE standard 7,542 floating-point arithmetic (corresponding to single and double precision respectively), plus support for the underlying processor, the operating system, and the compiler.

Inaccurate means that some values cannot be accurately converted into internal formats and are stored in an approximate form, so storing and then printing the data again may show some missing. Dealing with these errors and how these errors are propagated in the calculation is a complete branch of mathematics and computer science, and we will not discuss it further here, the discussion is limited to the following points:

• If you require accurate calculations (such as calculating currency amounts), you should use the numeric type.

• If you want to use these types for any important complex calculations, especially those that are heavily dependent on the extent of the situation (infinity, underflow), you should review gu your implementation.

• A comparison of two floating-point values for equality may or may not work as expected.

Typically, areal type has a range of at least -1E+37 to +1E+37, with a precision of at least 6 decimal places. Double precision types typically have a range of -1E+308 to +1E+308, with a precision of at least 15 digits. Values that are too large or too small can cause errors. If the input data is too high, then the rounding may occur. Numbers that are too close to 0 can create underflow errors if they cannot be distinguished from the representation of the 0 value.

In addition to the normal numeric values, there are several special values for floating-point types:

Infinity
-infinity
NaN

These values represent IEEE 754 special values, respectively"Positive Infinity","negative infinity", and "not a number". (on machines that do not follow IEEE 754 floating-point arithmetic, the meanings of these values may not be expected.) If you write these values as constants in a SQL command, you must enclose them in single quotes, such as UPDATE table SET x = ' Infinity '. When entered, these values are identified in case-insensitive manner.

postgresql  also supports SQL standard notation  float   and   float ( P )   is used to declare non-precise numeric types. Here, P   declares the lowest acceptable precision represented by bits. postgresql  accept   when selecting  real   Type float (1)   to  float , selecting  double Precision  , accept  float   to  float (+) .   p   values outside the allowable range will result in an error.  float with no declared precision will be treated as  double precision .

Note: before PostgreSQL 7.4, the precision inside the float (p) is considered to be the decimal bit of so many digits. To 7.4 has been modified to match the SQL standard, the standard declares that the accuracy is measured in bits. It is assumed that both the real and double precision have 24 and 53 bits digits that are correct for the IEEE standard floating-point implementation. On non-IEEE platforms, this value may be slightly biased, but for simplicity, we use the same p -value range on all platforms.

8.1.4. Serial (serial number) type

The serial and bigserial types are not true types, but are simply a conceptual convenience for setting unique identities in tables. (similar to the auto_increment attribute in some other databases). In the current implementation, the following sentence:

TableName (colname SERIAL);

Equivalent to declaring the following sentence:

TableName_colnametablename(colname integer DEFAULT nextval ('tablename _colname_seq ') not NULL);

Therefore, we create an integer field and schedule its default value to be taken from a sequence generator. A not NULL constraint is applied to ensure that null values are not explicitly inserted. In most cases you may also want to attach a UNIQUE or PRIMARY KEY constraint to avoid accidentally inserting duplicate values, but this does not happen automatically.

Note: prior to PostgreSQL 7.3,serial implied UNIQUE. But this is not the case now. If you want a sequence field to have a unique constraint or a primary key, you must now declare it, just like any other data type.

To use the serial field to insert the next value of the sequence into the table, it is important to note that the serial should be given the default value. We can do this by excluding the field from the field list in the INSERT statement, or by using the DEFAULT keyword.

The type names serial and Serial4 are equivalent: All two create an integer field. The type name bigserial and Serial8 are the same, except that it creates a bigint field. If you expect to use more than 231 identities during the lifetime of a table, you should use bigserial.

A sequence created by a serial type is automatically deleted when the owning field is deleted, and is not deleted in other cases. (This is not true until PostgreSQL version 7.3.) Note that this auto-delete association does not occur automatically when you dump a database that was previously overloaded with 7.3, and the dump file does not contain the information that you need to establish this association relationship. In addition, the dependencies between such sequences and fields are only available on the serial field itself, and if any other fields refer to the sequence (possibly by hand, the nextval function is called), then if the sequence is deleted, they will be destroyed. We think that using the serial field in this way is a bad form; If you want to feed data to several fields using the same sequence generator, create the sequence generator as a standalone object.

PostgreSQL data type