{"Entry":{"collection":"type","key":"serial","name":"serial","aliases":["serial4"],"metadata":{"aliases":["serial4"],"category":"SQL conveniences","content_hash":"366a12244cfec2fa51d4a463792ce78ead77f53ed22eec267cf06f4d5463b6a0","imported_at":"2026-09-30T00:40:36.650608+08:00","name":"serial","name_zh":"","slug":"serial","summary":"autoincrementing four-byte integer"}},"Definition":{"Collection":"type","Key":"serial","SourceDatabase":"center","Version":"18","SourceTable":"data_type","SourceKey":"serial","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":["serial4"],"casts":[],"catalog":{},"coverage":"documented type family or SQL syntax; not a catalog object","description":["autoincrementing four-byte integer"],"facts":[{"label":"Object boundary","value":"SQL convenience; no pg_type row"}],"manual_html":"\u003cdiv class=\"sect1\" id=\"DATATYPE-NUMERIC\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.1. Numeric Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eNumeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. \u003ca class=\"xref\" href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" title=\"Table 8.2. Numeric Types\"\u003eTable 8.2\u003c/a\u003e lists the available types.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"DATATYPE-NUMERIC-TABLE\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.2. Numeric Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eName\u003c/th\u003e\n\u003cth\u003eStorage Size\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003cth\u003eRange\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003esmallint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall-range integer\u003c/td\u003e\n\u003ctd\u003e-32768 to +32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003einteger\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003etypical choice for integer\u003c/td\u003e\n\u003ctd\u003e-2147483648 to +2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ebigint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge-range integer\u003c/td\u003e\n\u003ctd\u003e-9223372036854775808 to +9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003edecimal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ereal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e6 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e15 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003eautoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cp\u003eThe syntax of constants for the numeric types is described in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS\" title=\"4.1.2. Constants\"\u003eSection 4.1.2\u003c/a\u003e. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to \u003ca class=\"xref\" href=\"/docs/18/functions.html\" title=\"Chapter 9. Functions and Operators\"\u003eChapter 9\u003c/a\u003e for more information. The following sections describe the types in detail.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-INT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.1. Integer Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e, \u003ccode class=\"type\"\u003einteger\u003c/code\u003e, and \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.\u003c/p\u003e\n\u003cp\u003eThe type \u003ccode class=\"type\"\u003einteger\u003c/code\u003e is the common choice, as it offers the best balance between range, storage size, and performance. The \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e type is generally only used if disk space is at a premium. The \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e type is designed to be used when the range of the \u003ccode class=\"type\"\u003einteger\u003c/code\u003e type is insufficient.\u003c/p\u003e\n\u003cp\u003eSQL only specifies the integer types \u003ccode class=\"type\"\u003einteger\u003c/code\u003e (or \u003ccode class=\"type\"\u003eint\u003c/code\u003e), \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e, and \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e. The type names \u003ccode class=\"type\"\u003eint2\u003c/code\u003e, \u003ccode class=\"type\"\u003eint4\u003c/code\u003e, and \u003ccode class=\"type\"\u003eint8\u003c/code\u003e are extensions, which are also used by some other SQL database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-NUMERIC-DECIMAL\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.2. Arbitrary Precision Numbers \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe type \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values are very slow compared to the integer types, or to the floating-point types described in the next section.\u003c/p\u003e\n\u003cp\u003eWe use the following terms below: The \u003cem class=\"firstterm\"\u003eprecision\u003c/em\u003e of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The \u003cem class=\"firstterm\"\u003escale\u003c/em\u003e of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.\u003c/p\u003e\n\u003cp\u003eBoth the maximum precision and the maximum scale of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column can be configured. To declare a column of type \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e use the syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003escale\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eThe precision must be positive, while the scale may be positive or negative (see below). Alternatively:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eselects a scale of 0. Specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC\n\u003c/pre\u003e\n\u003cp\u003ewithout any precision or scale creates an \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eunconstrained numeric\u003c/span\u003e”\u003c/span\u003e column in which numeric values of any length can be stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThe maximum precision that can be explicitly specified in a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type declaration is 1000. An unconstrained \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column is subject to the limits described in \u003ca class=\"xref\" href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" title=\"Table 8.2. Numeric Types\"\u003eTable 8.2\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIf the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised. For example, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(3, 1)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 1 decimal place and can store values between -99.9 and 99.9, inclusive.\u003c/p\u003e\n\u003cp\u003eBeginning in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 15, it is allowed to declare a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column with a negative scale. Then values will be rounded to the left of the decimal point. The precision still represents the maximum number of non-rounded digits. Thus, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(2, -3)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to the nearest thousand and can store values between -99000 and 99000, inclusive. It is also allowed to declare a scale larger than the declared precision. Such a column can only hold fractional values, and it requires the number of zero digits just to the right of the decimal point to be at least the declared scale minus the declared precision. For example, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(3, 5)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 5 decimal places and can store values between -0.00999 and 0.00999, inclusive.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e permits the scale in a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type declaration to be any value in the range -1000 to 1000. However, the SQL standard requires the scale to be in the range 0 to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e. Using scales outside that range may not be portable to other database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type is more akin to \u003ccode class=\"type\"\u003evarchar(\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e than to \u003ccode class=\"type\"\u003echar(\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.\u003c/p\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type has several special values:\u003c/p\u003e\n\u003cdiv class=\"literallayout\"\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese are adapted from the IEEE 754 standard, and represent \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode class=\"literal\"\u003eUPDATE table SET x = '-Infinity'\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode class=\"literal\"\u003einf\u003c/code\u003e and \u003ccode class=\"literal\"\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe infinity values behave as per mathematical expectations. For example, \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e plus any finite value equals \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e, as does \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e plus \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e; but \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e minus \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e yields \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e (not a number), because it has no well-defined interpretation. Note that an infinity can only be stored in an unconstrained \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column, because it notionally exceeds any finite precision limit.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e (not a number) value is used to represent undefined calculational results. In general, any operation with a \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e input yields another \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e. The only exception is when the operation's other inputs are such that the same output would be obtained if the \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e were to be replaced by any finite or infinite numeric value; then, that output value is used for \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e too. (An example of this principle is that \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e raised to the zero power yields one.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eIn most implementations of the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e concept, \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e is not considered equal to any other numeric value (including \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e). In order to allow \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values to be sorted and used in tree-based indexes, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e treats \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode class=\"type\"\u003edecimal\u003c/code\u003e and \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e are equivalent. Both types are part of the SQL standard.\u003c/p\u003e\n\u003cp\u003eWhen rounding values, the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type rounds ties away from zero, while (on most machines) the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e and \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e types round ties to the nearest even number. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT x,\n  round(x::numeric) AS num_round,\n  round(x::double precision) AS dbl_round\nFROM generate_series(-3.5, 3.5, 1) as x;\n  x   | num_round | dbl_round\n------+-----------+-----------\n -3.5 |        -4 |        -4\n -2.5 |        -3 |        -2\n -1.5 |        -2 |        -2\n -0.5 |        -1 |        -0\n  0.5 |         1 |         0\n  1.5 |         2 |         2\n  2.5 |         3 |         2\n  3.5 |         4 |         4\n(8 rows)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-FLOAT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.3. Floating-Point Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode class=\"type\"\u003ereal\u003c/code\u003e and \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.\u003c/p\u003e\n\u003cp\u003eInexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf you require exact storage and calculations (such as for monetary amounts), use the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type instead.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eComparing two floating-point values for equality might not always work as expected.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eOn all currently supported platforms, the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e type has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.\u003c/p\u003e\n\u003cp\u003eBy default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never \u003cspan class=\"emphasis\"\u003e\u003cem\u003eexactly\u003c/em\u003e\u003c/span\u003e midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use at most 17 significant decimal digits for \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e values, and at most 9 digits for \u003ccode class=\"type\"\u003efloat4\u003c/code\u003e values.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis shortest-precise output format is much faster to generate than the historical rounded format.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eFor compatibility with output generated by older versions of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, and to allow the output precision to be reduced, the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for \u003ccode class=\"type\"\u003efloat4\u003c/code\u003e) or 15 (for \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e) significant decimal digits. Setting a negative value reduces the number of digits further; for example -2 would round output to 4 or 13 digits respectively.\u003c/p\u003e\n\u003cp\u003eAny value of \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e greater than 0 selects the shortest-precise format.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eApplications that wanted precise values have historically had to set \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e to 3 to obtain them. For maximum compatibility between versions, they should continue to do so.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the floating-point types have several special values:\u003c/p\u003e\n\u003cdiv class=\"literallayout\"\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese represent the IEEE 754 special values \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode class=\"literal\"\u003eUPDATE table SET x = '-Infinity'\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode class=\"literal\"\u003einf\u003c/code\u003e and \u003ccode class=\"literal\"\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eIEEE 754 specifies that \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e should not compare equal to any other floating-point value (including \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e). In order to allow floating-point values to be sorted and used in tree-based indexes, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e treats \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e also supports the SQL-standard notations \u003ccode class=\"type\"\u003efloat\u003c/code\u003e and \u003ccode class=\"type\"\u003efloat(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e for specifying inexact numeric types. Here, \u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e specifies the minimum acceptable precision in \u003cspan class=\"emphasis\"\u003e\u003cem\u003ebinary\u003c/em\u003e\u003c/span\u003e digits. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e accepts \u003ccode class=\"type\"\u003efloat(1)\u003c/code\u003e to \u003ccode class=\"type\"\u003efloat(24)\u003c/code\u003e as selecting the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e type, while \u003ccode class=\"type\"\u003efloat(25)\u003c/code\u003e to \u003ccode class=\"type\"\u003efloat(53)\u003c/code\u003e select \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e. Values of \u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e outside the allowed range draw an error. \u003ccode class=\"type\"\u003efloat\u003c/code\u003e with no precision specified is taken to mean \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-SERIAL\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.4. Serial Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis section describes a PostgreSQL-specific way to create an autoincrementing column. Another way is to use the SQL-standard identity column feature, described at \u003ca class=\"xref\" href=\"/docs/18/ddl-identity-columns.html\" title=\"5.3. Identity Columns\"\u003eSection 5.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e, \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e are not true types, but merely a notational convenience for creating unique identifier columns (similar to the \u003ccode class=\"literal\"\u003eAUTO_INCREMENT\u003c/code\u003e property supported by some other databases). In the current implementation, specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e SERIAL\n);\n\u003c/pre\u003e\n\u003cp\u003eis equivalent to specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE SEQUENCE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq AS integer;\nCREATE TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e integer NOT NULL DEFAULT nextval('\u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq')\n);\nALTER SEQUENCE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq OWNED BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\n\u003cp\u003eThus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a \u003ccode class=\"literal\"\u003eUNIQUE\u003c/code\u003e or \u003ccode class=\"literal\"\u003ePRIMARY KEY\u003c/code\u003e constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eowned by\u003c/span\u003e”\u003c/span\u003e the column, so that it will be dropped if the column or table is dropped.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eBecause \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e, \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e are implemented using sequences, there may be \"holes\" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still \"used up\" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See \u003ccode class=\"literal\"\u003enextval()\u003c/code\u003e in \u003ca class=\"xref\" href=\"/docs/18/functions-sequence.html\" title=\"9.17. Sequence Manipulation Functions\"\u003eSection 9.17\u003c/a\u003e for details.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eTo insert the next value of the sequence into the \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column, specify that the \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column should be assigned its default value. This can be done either by excluding the column from the list of columns in the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statement, or through the use of the \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e key word.\u003c/p\u003e\n\u003cp\u003eThe type names \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial4\u003c/code\u003e are equivalent: both create \u003ccode class=\"type\"\u003einteger\u003c/code\u003e columns. The type names \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial8\u003c/code\u003e work the same way, except that they create a \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e column. \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e should be used if you anticipate the use of more than 2\u003csup\u003e31\u003c/sup\u003e identifiers over the lifetime of the table. The type names \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial2\u003c/code\u003e also work the same way, except that they create a \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e column.\u003c/p\u003e\n\u003cp\u003eThe sequence created for a \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"datatype-numeric.html","operator_classes":[],"operators":[],"ranges":[],"related":[],"release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"serial","sources":[{"label":"PostgreSQL 18 English manual","path":"datatype-numeric.html","sha256":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","url":"/docs/18/datatype-numeric.html"}]},"ManualEvidence":{"manual_path":"datatype-numeric.html","release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"datatype-numeric.html","sha256":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","url":"/docs/18/datatype-numeric.html"}]},"MeasuredEvidence":{}},"Text":{"Collection":"type","Key":"serial","SourceDatabase":"center","Version":"18","Locale":"en","Title":"serial","Summary":"autoincrementing four-byte integer","BodyHTML":"\u003cdiv id=\"DATATYPE-NUMERIC\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e8.1. Numeric Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eNumeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. \u003ca href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" rel=\"nofollow\"\u003eTable 8.2\u003c/a\u003e lists the available types.\u003c/p\u003e\n\u003cdiv id=\"DATATYPE-NUMERIC-TABLE\"\u003e\n\u003cp\u003e\u003cstrong\u003eTable 8.2. Numeric Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003ctable\u003e\n\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eName\u003c/th\u003e\n\u003cth\u003eStorage Size\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003cth\u003eRange\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003esmallint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall-range integer\u003c/td\u003e\n\u003ctd\u003e-32768 to +32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003einteger\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003etypical choice for integer\u003c/td\u003e\n\u003ctd\u003e-2147483648 to +2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003ebigint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge-range integer\u003c/td\u003e\n\u003ctd\u003e-9223372036854775808 to +9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003edecimal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003ereal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e6 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003edouble precision\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e15 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003esmallserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003eserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003eautoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003ebigserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003cp\u003eThe syntax of constants for the numeric types is described in \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS\" rel=\"nofollow\"\u003eSection 4.1.2\u003c/a\u003e. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to \u003ca href=\"/docs/18/functions.html\" rel=\"nofollow\"\u003eChapter 9\u003c/a\u003e for more information. The following sections describe the types in detail.\u003c/p\u003e\n\u003cdiv id=\"DATATYPE-INT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.1.1. Integer Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode\u003esmallint\u003c/code\u003e, \u003ccode\u003einteger\u003c/code\u003e, and \u003ccode\u003ebigint\u003c/code\u003e store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.\u003c/p\u003e\n\u003cp\u003eThe type \u003ccode\u003einteger\u003c/code\u003e is the common choice, as it offers the best balance between range, storage size, and performance. The \u003ccode\u003esmallint\u003c/code\u003e type is generally only used if disk space is at a premium. The \u003ccode\u003ebigint\u003c/code\u003e type is designed to be used when the range of the \u003ccode\u003einteger\u003c/code\u003e type is insufficient.\u003c/p\u003e\n\u003cp\u003eSQL only specifies the integer types \u003ccode\u003einteger\u003c/code\u003e (or \u003ccode\u003eint\u003c/code\u003e), \u003ccode\u003esmallint\u003c/code\u003e, and \u003ccode\u003ebigint\u003c/code\u003e. The type names \u003ccode\u003eint2\u003c/code\u003e, \u003ccode\u003eint4\u003c/code\u003e, and \u003ccode\u003eint8\u003c/code\u003e are extensions, which are also used by some other SQL database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"DATATYPE-NUMERIC-DECIMAL\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.1.2. Arbitrary Precision Numbers \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe type \u003ccode\u003enumeric\u003c/code\u003e can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with \u003ccode\u003enumeric\u003c/code\u003e values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on \u003ccode\u003enumeric\u003c/code\u003e values are very slow compared to the integer types, or to the floating-point types described in the next section.\u003c/p\u003e\n\u003cp\u003eWe use the following terms below: The \u003cem\u003eprecision\u003c/em\u003e of a \u003ccode\u003enumeric\u003c/code\u003e is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The \u003cem\u003escale\u003c/em\u003e of a \u003ccode\u003enumeric\u003c/code\u003e is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.\u003c/p\u003e\n\u003cp\u003eBoth the maximum precision and the maximum scale of a \u003ccode\u003enumeric\u003c/code\u003e column can be configured. To declare a column of type \u003ccode\u003enumeric\u003c/code\u003e use the syntax:\u003c/p\u003e\n\u003cpre\u003eNUMERIC(\u003cem\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e, \u003cem\u003e\u003ccode\u003escale\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eThe precision must be positive, while the scale may be positive or negative (see below). Alternatively:\u003c/p\u003e\n\u003cpre\u003eNUMERIC(\u003cem\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eselects a scale of 0. Specifying:\u003c/p\u003e\n\u003cpre\u003eNUMERIC\n\u003c/pre\u003e\n\u003cp\u003ewithout any precision or scale creates an \u003cspan\u003e“\u003cspan\u003eunconstrained numeric\u003c/span\u003e”\u003c/span\u003e column in which numeric values of any length can be stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas \u003ccode\u003enumeric\u003c/code\u003e columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you\u0026#39;re concerned about portability, always specify the precision and scale explicitly.)\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eThe maximum precision that can be explicitly specified in a \u003ccode\u003enumeric\u003c/code\u003e type declaration is 1000. An unconstrained \u003ccode\u003enumeric\u003c/code\u003e column is subject to the limits described in \u003ca href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" rel=\"nofollow\"\u003eTable 8.2\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIf the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised. For example, a column declared as\u003c/p\u003e\n\u003cpre\u003eNUMERIC(3, 1)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 1 decimal place and can store values between -99.9 and 99.9, inclusive.\u003c/p\u003e\n\u003cp\u003eBeginning in \u003cspan\u003ePostgreSQL\u003c/span\u003e 15, it is allowed to declare a \u003ccode\u003enumeric\u003c/code\u003e column with a negative scale. Then values will be rounded to the left of the decimal point. The precision still represents the maximum number of non-rounded digits. Thus, a column declared as\u003c/p\u003e\n\u003cpre\u003eNUMERIC(2, -3)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to the nearest thousand and can store values between -99000 and 99000, inclusive. It is also allowed to declare a scale larger than the declared precision. Such a column can only hold fractional values, and it requires the number of zero digits just to the right of the decimal point to be at least the declared scale minus the declared precision. For example, a column declared as\u003c/p\u003e\n\u003cpre\u003eNUMERIC(3, 5)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 5 decimal places and can store values between -0.00999 and 0.00999, inclusive.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e permits the scale in a \u003ccode\u003enumeric\u003c/code\u003e type declaration to be any value in the range -1000 to 1000. However, the SQL standard requires the scale to be in the range 0 to \u003cem\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e. Using scales outside that range may not be portable to other database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the \u003ccode\u003enumeric\u003c/code\u003e type is more akin to \u003ccode\u003evarchar(\u003cem\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e than to \u003ccode\u003echar(\u003cem\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.\u003c/p\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the \u003ccode\u003enumeric\u003c/code\u003e type has several special values:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese are adapted from the IEEE 754 standard, and represent \u003cspan\u003e“\u003cspan\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan\u003e“\u003cspan\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan\u003e“\u003cspan\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode\u003eUPDATE table SET x = \u0026#39;-Infinity\u0026#39;\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode\u003einf\u003c/code\u003e and \u003ccode\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe infinity values behave as per mathematical expectations. For example, \u003ccode\u003eInfinity\u003c/code\u003e plus any finite value equals \u003ccode\u003eInfinity\u003c/code\u003e, as does \u003ccode\u003eInfinity\u003c/code\u003e plus \u003ccode\u003eInfinity\u003c/code\u003e; but \u003ccode\u003eInfinity\u003c/code\u003e minus \u003ccode\u003eInfinity\u003c/code\u003e yields \u003ccode\u003eNaN\u003c/code\u003e (not a number), because it has no well-defined interpretation. Note that an infinity can only be stored in an unconstrained \u003ccode\u003enumeric\u003c/code\u003e column, because it notionally exceeds any finite precision limit.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode\u003eNaN\u003c/code\u003e (not a number) value is used to represent undefined calculational results. In general, any operation with a \u003ccode\u003eNaN\u003c/code\u003e input yields another \u003ccode\u003eNaN\u003c/code\u003e. The only exception is when the operation\u0026#39;s other inputs are such that the same output would be obtained if the \u003ccode\u003eNaN\u003c/code\u003e were to be replaced by any finite or infinite numeric value; then, that output value is used for \u003ccode\u003eNaN\u003c/code\u003e too. (An example of this principle is that \u003ccode\u003eNaN\u003c/code\u003e raised to the zero power yields one.)\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eIn most implementations of the \u003cspan\u003e“\u003cspan\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e concept, \u003ccode\u003eNaN\u003c/code\u003e is not considered equal to any other numeric value (including \u003ccode\u003eNaN\u003c/code\u003e). In order to allow \u003ccode\u003enumeric\u003c/code\u003e values to be sorted and used in tree-based indexes, \u003cspan\u003ePostgreSQL\u003c/span\u003e treats \u003ccode\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode\u003edecimal\u003c/code\u003e and \u003ccode\u003enumeric\u003c/code\u003e are equivalent. Both types are part of the SQL standard.\u003c/p\u003e\n\u003cp\u003eWhen rounding values, the \u003ccode\u003enumeric\u003c/code\u003e type rounds ties away from zero, while (on most machines) the \u003ccode\u003ereal\u003c/code\u003e and \u003ccode\u003edouble precision\u003c/code\u003e types round ties to the nearest even number. For example:\u003c/p\u003e\n\u003cpre\u003eSELECT x,\n  round(x::numeric) AS num_round,\n  round(x::double precision) AS dbl_round\nFROM generate_series(-3.5, 3.5, 1) as x;\n  x   | num_round | dbl_round\n------+-----------+-----------\n -3.5 |        -4 |        -4\n -2.5 |        -3 |        -2\n -1.5 |        -2 |        -2\n -0.5 |        -1 |        -0\n  0.5 |         1 |         0\n  1.5 |         2 |         2\n  2.5 |         3 |         2\n  3.5 |         4 |         4\n(8 rows)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv id=\"DATATYPE-FLOAT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.1.3. Floating-Point Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode\u003ereal\u003c/code\u003e and \u003ccode\u003edouble precision\u003c/code\u003e are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.\u003c/p\u003e\n\u003cp\u003eInexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003eIf you require exact storage and calculations (such as for monetary amounts), use the \u003ccode\u003enumeric\u003c/code\u003e type instead.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eIf you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eComparing two floating-point values for equality might not always work as expected.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eOn all currently supported platforms, the \u003ccode\u003ereal\u003c/code\u003e type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The \u003ccode\u003edouble precision\u003c/code\u003e type has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.\u003c/p\u003e\n\u003cp\u003eBy default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never \u003cspan\u003e\u003cem\u003eexactly\u003c/em\u003e\u003c/span\u003e midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use at most 17 significant decimal digits for \u003ccode\u003efloat8\u003c/code\u003e values, and at most 9 digits for \u003ccode\u003efloat4\u003c/code\u003e values.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis shortest-precise output format is much faster to generate than the historical rounded format.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eFor compatibility with output generated by older versions of \u003cspan\u003ePostgreSQL\u003c/span\u003e, and to allow the output precision to be reduced, the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\" rel=\"nofollow\"\u003eextra_float_digits\u003c/a\u003e parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for \u003ccode\u003efloat4\u003c/code\u003e) or 15 (for \u003ccode\u003efloat8\u003c/code\u003e) significant decimal digits. Setting a negative value reduces the number of digits further; for example -2 would round output to 4 or 13 digits respectively.\u003c/p\u003e\n\u003cp\u003eAny value of \u003ca href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\" rel=\"nofollow\"\u003eextra_float_digits\u003c/a\u003e greater than 0 selects the shortest-precise format.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eApplications that wanted precise values have historically had to set \u003ca href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\" rel=\"nofollow\"\u003eextra_float_digits\u003c/a\u003e to 3 to obtain them. For maximum compatibility between versions, they should continue to do so.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the floating-point types have several special values:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese represent the IEEE 754 special values \u003cspan\u003e“\u003cspan\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan\u003e“\u003cspan\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan\u003e“\u003cspan\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode\u003eUPDATE table SET x = \u0026#39;-Infinity\u0026#39;\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode\u003einf\u003c/code\u003e and \u003ccode\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eIEEE 754 specifies that \u003ccode\u003eNaN\u003c/code\u003e should not compare equal to any other floating-point value (including \u003ccode\u003eNaN\u003c/code\u003e). In order to allow floating-point values to be sorted and used in tree-based indexes, \u003cspan\u003ePostgreSQL\u003c/span\u003e treats \u003ccode\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e also supports the SQL-standard notations \u003ccode\u003efloat\u003c/code\u003e and \u003ccode\u003efloat(\u003cem\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e for specifying inexact numeric types. Here, \u003cem\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e specifies the minimum acceptable precision in \u003cspan\u003e\u003cem\u003ebinary\u003c/em\u003e\u003c/span\u003e digits. \u003cspan\u003ePostgreSQL\u003c/span\u003e accepts \u003ccode\u003efloat(1)\u003c/code\u003e to \u003ccode\u003efloat(24)\u003c/code\u003e as selecting the \u003ccode\u003ereal\u003c/code\u003e type, while \u003ccode\u003efloat(25)\u003c/code\u003e to \u003ccode\u003efloat(53)\u003c/code\u003e select \u003ccode\u003edouble precision\u003c/code\u003e. Values of \u003cem\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e outside the allowed range draw an error. \u003ccode\u003efloat\u003c/code\u003e with no precision specified is taken to mean \u003ccode\u003edouble precision\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"DATATYPE-SERIAL\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.1.4. Serial Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis section describes a PostgreSQL-specific way to create an autoincrementing column. Another way is to use the SQL-standard identity column feature, described at \u003ca href=\"/docs/18/ddl-identity-columns.html\" rel=\"nofollow\"\u003eSection 5.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode\u003esmallserial\u003c/code\u003e, \u003ccode\u003eserial\u003c/code\u003e and \u003ccode\u003ebigserial\u003c/code\u003e are not true types, but merely a notational convenience for creating unique identifier columns (similar to the \u003ccode\u003eAUTO_INCREMENT\u003c/code\u003e property supported by some other databases). In the current implementation, specifying:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE \u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e SERIAL\n);\n\u003c/pre\u003e\n\u003cp\u003eis equivalent to specifying:\u003c/p\u003e\n\u003cpre\u003eCREATE SEQUENCE \u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq AS integer;\nCREATE TABLE \u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e integer NOT NULL DEFAULT nextval(\u0026#39;\u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq\u0026#39;)\n);\nALTER SEQUENCE \u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq OWNED BY \u003cem\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e.\u003cem\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\n\u003cp\u003eThus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A \u003ccode\u003eNOT NULL\u003c/code\u003e constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a \u003ccode\u003eUNIQUE\u003c/code\u003e or \u003ccode\u003ePRIMARY KEY\u003c/code\u003e constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as \u003cspan\u003e“\u003cspan\u003eowned by\u003c/span\u003e”\u003c/span\u003e the column, so that it will be dropped if the column or table is dropped.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eBecause \u003ccode\u003esmallserial\u003c/code\u003e, \u003ccode\u003eserial\u003c/code\u003e and \u003ccode\u003ebigserial\u003c/code\u003e are implemented using sequences, there may be \u0026#34;holes\u0026#34; or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still \u0026#34;used up\u0026#34; even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See \u003ccode\u003enextval()\u003c/code\u003e in \u003ca href=\"/docs/18/functions-sequence.html\" rel=\"nofollow\"\u003eSection 9.17\u003c/a\u003e for details.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eTo insert the next value of the sequence into the \u003ccode\u003eserial\u003c/code\u003e column, specify that the \u003ccode\u003eserial\u003c/code\u003e column should be assigned its default value. This can be done either by excluding the column from the list of columns in the \u003ccode\u003eINSERT\u003c/code\u003e statement, or through the use of the \u003ccode\u003eDEFAULT\u003c/code\u003e key word.\u003c/p\u003e\n\u003cp\u003eThe type names \u003ccode\u003eserial\u003c/code\u003e and \u003ccode\u003eserial4\u003c/code\u003e are equivalent: both create \u003ccode\u003einteger\u003c/code\u003e columns. The type names \u003ccode\u003ebigserial\u003c/code\u003e and \u003ccode\u003eserial8\u003c/code\u003e work the same way, except that they create a \u003ccode\u003ebigint\u003c/code\u003e column. \u003ccode\u003ebigserial\u003c/code\u003e should be used if you anticipate the use of more than 2\u003csup\u003e31\u003c/sup\u003e identifiers over the lifetime of the table. The type names \u003ccode\u003esmallserial\u003c/code\u003e and \u003ccode\u003eserial2\u003c/code\u003e also work the same way, except that they create a \u003ccode\u003esmallint\u003c/code\u003e column.\u003c/p\u003e\n\u003cp\u003eThe sequence created for a \u003ccode\u003eserial\u003c/code\u003e column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"1d215ff1599218225b008aa8c4798824e16feaad517f6c7b3a3a882bf43e7567","Payload":{"description":["autoincrementing four-byte integer"],"manual_html":"\u003cdiv class=\"sect1\" id=\"DATATYPE-NUMERIC\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.1. Numeric Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eNumeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. \u003ca class=\"xref\" href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" title=\"Table 8.2. Numeric Types\"\u003eTable 8.2\u003c/a\u003e lists the available types.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"DATATYPE-NUMERIC-TABLE\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.2. Numeric Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eName\u003c/th\u003e\n\u003cth\u003eStorage Size\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003cth\u003eRange\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003esmallint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall-range integer\u003c/td\u003e\n\u003ctd\u003e-32768 to +32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003einteger\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003etypical choice for integer\u003c/td\u003e\n\u003ctd\u003e-2147483648 to +2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ebigint\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge-range integer\u003c/td\u003e\n\u003ctd\u003e-9223372036854775808 to +9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003edecimal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003evariable\u003c/td\u003e\n\u003ctd\u003euser-specified precision, exact\u003c/td\u003e\n\u003ctd\u003eup to 131072 digits before the decimal point; up to 16383 digits after the decimal point\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ereal\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e6 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003evariable-precision, inexact\u003c/td\u003e\n\u003ctd\u003e15 decimal digits precision\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e2 bytes\u003c/td\u003e\n\u003ctd\u003esmall autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 32767\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e4 bytes\u003c/td\u003e\n\u003ctd\u003eautoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 2147483647\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e8 bytes\u003c/td\u003e\n\u003ctd\u003elarge autoincrementing integer\u003c/td\u003e\n\u003ctd\u003e1 to 9223372036854775807\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cp\u003eThe syntax of constants for the numeric types is described in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS\" title=\"4.1.2. Constants\"\u003eSection 4.1.2\u003c/a\u003e. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to \u003ca class=\"xref\" href=\"/docs/18/functions.html\" title=\"Chapter 9. Functions and Operators\"\u003eChapter 9\u003c/a\u003e for more information. The following sections describe the types in detail.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-INT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.1. Integer Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e, \u003ccode class=\"type\"\u003einteger\u003c/code\u003e, and \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.\u003c/p\u003e\n\u003cp\u003eThe type \u003ccode class=\"type\"\u003einteger\u003c/code\u003e is the common choice, as it offers the best balance between range, storage size, and performance. The \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e type is generally only used if disk space is at a premium. The \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e type is designed to be used when the range of the \u003ccode class=\"type\"\u003einteger\u003c/code\u003e type is insufficient.\u003c/p\u003e\n\u003cp\u003eSQL only specifies the integer types \u003ccode class=\"type\"\u003einteger\u003c/code\u003e (or \u003ccode class=\"type\"\u003eint\u003c/code\u003e), \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e, and \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e. The type names \u003ccode class=\"type\"\u003eint2\u003c/code\u003e, \u003ccode class=\"type\"\u003eint4\u003c/code\u003e, and \u003ccode class=\"type\"\u003eint8\u003c/code\u003e are extensions, which are also used by some other SQL database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-NUMERIC-DECIMAL\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.2. Arbitrary Precision Numbers \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe type \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values are very slow compared to the integer types, or to the floating-point types described in the next section.\u003c/p\u003e\n\u003cp\u003eWe use the following terms below: The \u003cem class=\"firstterm\"\u003eprecision\u003c/em\u003e of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The \u003cem class=\"firstterm\"\u003escale\u003c/em\u003e of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.\u003c/p\u003e\n\u003cp\u003eBoth the maximum precision and the maximum scale of a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column can be configured. To declare a column of type \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e use the syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003escale\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eThe precision must be positive, while the scale may be positive or negative (see below). Alternatively:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e)\n\u003c/pre\u003e\n\u003cp\u003eselects a scale of 0. Specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC\n\u003c/pre\u003e\n\u003cp\u003ewithout any precision or scale creates an \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eunconstrained numeric\u003c/span\u003e”\u003c/span\u003e column in which numeric values of any length can be stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThe maximum precision that can be explicitly specified in a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type declaration is 1000. An unconstrained \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column is subject to the limits described in \u003ca class=\"xref\" href=\"/docs/18/datatype-numeric.html#DATATYPE-NUMERIC-TABLE\" title=\"Table 8.2. Numeric Types\"\u003eTable 8.2\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIf the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised. For example, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(3, 1)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 1 decimal place and can store values between -99.9 and 99.9, inclusive.\u003c/p\u003e\n\u003cp\u003eBeginning in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 15, it is allowed to declare a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column with a negative scale. Then values will be rounded to the left of the decimal point. The precision still represents the maximum number of non-rounded digits. Thus, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(2, -3)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to the nearest thousand and can store values between -99000 and 99000, inclusive. It is also allowed to declare a scale larger than the declared precision. Such a column can only hold fractional values, and it requires the number of zero digits just to the right of the decimal point to be at least the declared scale minus the declared precision. For example, a column declared as\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eNUMERIC(3, 5)\n\u003c/pre\u003e\n\u003cp\u003ewill round values to 5 decimal places and can store values between -0.00999 and 0.00999, inclusive.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e permits the scale in a \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type declaration to be any value in the range -1000 to 1000. However, the SQL standard requires the scale to be in the range 0 to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprecision\u003c/code\u003e\u003c/em\u003e. Using scales outside that range may not be portable to other database systems.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type is more akin to \u003ccode class=\"type\"\u003evarchar(\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e than to \u003ccode class=\"type\"\u003echar(\u003cem class=\"replaceable\"\u003e\u003ccode\u003en\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.\u003c/p\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type has several special values:\u003c/p\u003e\n\u003cdiv class=\"literallayout\"\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese are adapted from the IEEE 754 standard, and represent \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode class=\"literal\"\u003eUPDATE table SET x = '-Infinity'\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode class=\"literal\"\u003einf\u003c/code\u003e and \u003ccode class=\"literal\"\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe infinity values behave as per mathematical expectations. For example, \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e plus any finite value equals \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e, as does \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e plus \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e; but \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e minus \u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e yields \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e (not a number), because it has no well-defined interpretation. Note that an infinity can only be stored in an unconstrained \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e column, because it notionally exceeds any finite precision limit.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e (not a number) value is used to represent undefined calculational results. In general, any operation with a \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e input yields another \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e. The only exception is when the operation's other inputs are such that the same output would be obtained if the \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e were to be replaced by any finite or infinite numeric value; then, that output value is used for \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e too. (An example of this principle is that \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e raised to the zero power yields one.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eIn most implementations of the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e concept, \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e is not considered equal to any other numeric value (including \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e). In order to allow \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e values to be sorted and used in tree-based indexes, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e treats \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe types \u003ccode class=\"type\"\u003edecimal\u003c/code\u003e and \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e are equivalent. Both types are part of the SQL standard.\u003c/p\u003e\n\u003cp\u003eWhen rounding values, the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type rounds ties away from zero, while (on most machines) the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e and \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e types round ties to the nearest even number. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT x,\n  round(x::numeric) AS num_round,\n  round(x::double precision) AS dbl_round\nFROM generate_series(-3.5, 3.5, 1) as x;\n  x   | num_round | dbl_round\n------+-----------+-----------\n -3.5 |        -4 |        -4\n -2.5 |        -3 |        -2\n -1.5 |        -2 |        -2\n -0.5 |        -1 |        -0\n  0.5 |         1 |         0\n  1.5 |         2 |         2\n  2.5 |         3 |         2\n  3.5 |         4 |         4\n(8 rows)\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-FLOAT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.3. Floating-Point Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode class=\"type\"\u003ereal\u003c/code\u003e and \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.\u003c/p\u003e\n\u003cp\u003eInexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf you require exact storage and calculations (such as for monetary amounts), use the \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type instead.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eComparing two floating-point values for equality might not always work as expected.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eOn all currently supported platforms, the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e type has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.\u003c/p\u003e\n\u003cp\u003eBy default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never \u003cspan class=\"emphasis\"\u003e\u003cem\u003eexactly\u003c/em\u003e\u003c/span\u003e midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use at most 17 significant decimal digits for \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e values, and at most 9 digits for \u003ccode class=\"type\"\u003efloat4\u003c/code\u003e values.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis shortest-precise output format is much faster to generate than the historical rounded format.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eFor compatibility with output generated by older versions of \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, and to allow the output precision to be reduced, the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for \u003ccode class=\"type\"\u003efloat4\u003c/code\u003e) or 15 (for \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e) significant decimal digits. Setting a negative value reduces the number of digits further; for example -2 would round output to 4 or 13 digits respectively.\u003c/p\u003e\n\u003cp\u003eAny value of \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e greater than 0 selects the shortest-precise format.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eApplications that wanted precise values have historically had to set \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS\"\u003eextra_float_digits\u003c/a\u003e to 3 to obtain them. For maximum compatibility between versions, they should continue to do so.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition to ordinary numeric values, the floating-point types have several special values:\u003c/p\u003e\n\u003cdiv class=\"literallayout\"\u003e\n\u003cp\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eInfinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003e-Infinity\u003c/code\u003e\u003cbr\u003e\n\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e\u003cbr\u003e\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThese represent the IEEE 754 special values \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enegative infinity\u003c/span\u003e”\u003c/span\u003e, and \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enot-a-number\u003c/span\u003e”\u003c/span\u003e, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example \u003ccode class=\"literal\"\u003eUPDATE table SET x = '-Infinity'\u003c/code\u003e. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled \u003ccode class=\"literal\"\u003einf\u003c/code\u003e and \u003ccode class=\"literal\"\u003e-inf\u003c/code\u003e.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eIEEE 754 specifies that \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e should not compare equal to any other floating-point value (including \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e). In order to allow floating-point values to be sorted and used in tree-based indexes, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e treats \u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values as equal, and greater than all non-\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e also supports the SQL-standard notations \u003ccode class=\"type\"\u003efloat\u003c/code\u003e and \u003ccode class=\"type\"\u003efloat(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e for specifying inexact numeric types. Here, \u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e specifies the minimum acceptable precision in \u003cspan class=\"emphasis\"\u003e\u003cem\u003ebinary\u003c/em\u003e\u003c/span\u003e digits. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e accepts \u003ccode class=\"type\"\u003efloat(1)\u003c/code\u003e to \u003ccode class=\"type\"\u003efloat(24)\u003c/code\u003e as selecting the \u003ccode class=\"type\"\u003ereal\u003c/code\u003e type, while \u003ccode class=\"type\"\u003efloat(25)\u003c/code\u003e to \u003ccode class=\"type\"\u003efloat(53)\u003c/code\u003e select \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e. Values of \u003cem class=\"replaceable\"\u003e\u003ccode\u003ep\u003c/code\u003e\u003c/em\u003e outside the allowed range draw an error. \u003ccode class=\"type\"\u003efloat\u003c/code\u003e with no precision specified is taken to mean \u003ccode class=\"type\"\u003edouble precision\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-SERIAL\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.1.4. Serial Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThis section describes a PostgreSQL-specific way to create an autoincrementing column. Another way is to use the SQL-standard identity column feature, described at \u003ca class=\"xref\" href=\"/docs/18/ddl-identity-columns.html\" title=\"5.3. Identity Columns\"\u003eSection 5.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe data types \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e, \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e are not true types, but merely a notational convenience for creating unique identifier columns (similar to the \u003ccode class=\"literal\"\u003eAUTO_INCREMENT\u003c/code\u003e property supported by some other databases). In the current implementation, specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e SERIAL\n);\n\u003c/pre\u003e\n\u003cp\u003eis equivalent to specifying:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE SEQUENCE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq AS integer;\nCREATE TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e (\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e integer NOT NULL DEFAULT nextval('\u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq')\n);\nALTER SEQUENCE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e_\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e_seq OWNED BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablename\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolname\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\n\u003cp\u003eThus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a \u003ccode class=\"literal\"\u003eUNIQUE\u003c/code\u003e or \u003ccode class=\"literal\"\u003ePRIMARY KEY\u003c/code\u003e constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eowned by\u003c/span\u003e”\u003c/span\u003e the column, so that it will be dropped if the column or table is dropped.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eBecause \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e, \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e are implemented using sequences, there may be \"holes\" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still \"used up\" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See \u003ccode class=\"literal\"\u003enextval()\u003c/code\u003e in \u003ca class=\"xref\" href=\"/docs/18/functions-sequence.html\" title=\"9.17. Sequence Manipulation Functions\"\u003eSection 9.17\u003c/a\u003e for details.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eTo insert the next value of the sequence into the \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column, specify that the \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column should be assigned its default value. This can be done either by excluding the column from the list of columns in the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statement, or through the use of the \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e key word.\u003c/p\u003e\n\u003cp\u003eThe type names \u003ccode class=\"type\"\u003eserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial4\u003c/code\u003e are equivalent: both create \u003ccode class=\"type\"\u003einteger\u003c/code\u003e columns. The type names \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial8\u003c/code\u003e work the same way, except that they create a \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e column. \u003ccode class=\"type\"\u003ebigserial\u003c/code\u003e should be used if you anticipate the use of more than 2\u003csup\u003e31\u003c/sup\u003e identifiers over the lifetime of the table. The type names \u003ccode class=\"type\"\u003esmallserial\u003c/code\u003e and \u003ccode class=\"type\"\u003eserial2\u003c/code\u003e also work the same way, except that they create a \u003ccode class=\"type\"\u003esmallint\u003c/code\u003e column.\u003c/p\u003e\n\u003cp\u003eThe sequence created for a \u003ccode class=\"type\"\u003eserial\u003c/code\u003e column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[],"sections":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}