Harper Boyd, Jr.. Table of Contents 8.1. 8.13.PostgreSQL has a rich set ofnative data types available to users. Users may add new types toPostgreSQL using the command.shows allthe built-in general-purpose data types. Most of the alternativenames listed in the 'Aliases' columnare the names used internally by PostgreSQL for historical reasons. Inaddition, some internally used or deprecated types are available,but they are not listed here. Compatibility: The following types (or spellingsthereof) are specified by SQL: bit,bit varying, boolean, char, character varying, character, varchar,date, doubleprecision, integer, interval, numeric,decimal, real,smallint, time(with or without time zone), timestamp(with or without time zone).Each data type has an external representation determined byits input and output functions. Many of the built-in types haveobvious external formats.

• Serial Postgres 10

Depesz already wrote a blog post about it and showed that it works pretty much like serial columns: CREATE TABLE testold ( id serial. The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. Bigserial should be used if you anticipate the use of more than 2 31 identifiers over the lifetime of the table.

Serial Postgres 10

However, several types are eitherunique to PostgreSQL, such asgeometric paths, or have several possibilities for formats, suchas the date and time types. Some of the input and outputfunctions are not invertible. That is, the result of an outputfunction may lose accuracy when compared to the originalinput. The types smallint, integer, and bigint storewhole numbers, that is, numbers without fractionalcomponents, of various ranges.

Attempts to store valuesoutside of the allowed range will result in an error.The type integer is the usualchoice, as it offers the best balance between range, storagesize, and performance. The smallinttype is generally only used if disk space is at a premium.The bigint type should only be used ifthe integer range is not sufficient,because the latter is definitely faster.The bigint type may not functioncorrectly on all platforms, since it relies on compilersupport for eight-byte integers. On a machine without suchsupport, bigint acts the same asinteger (but still takes up eight bytesof storage). However, we are not aware of any reasonableplatform where this is actually the case.SQL only specifies theinteger types integer (or int) and smallint. The typebigint, and the type names int2, int4, and int8 are extensions, which are shared withvarious other SQL databasesystems. The type numeric can store numberswith up to 1000 digits of precision and perform calculationsexactly.

It is especially recommended for storing monetaryamounts and other quantities where exactness is required.However, arithmetic on numeric valuesis very slow compared to the integer types, or to thefloating-point types described in the next section.In what follows we use these terms: The scale of a numeric isthe count of decimal digits in the fractional part, to theright of the decimal point. The precision of a numericis the total count of significant digits in the whole number,that is, the number of digits to both sides of the decimalpoint. So the number 23.5141 has a precision of 6 and a scaleof 4.

Integers can be considered to have a scale of zero.Both the maximum precision and the maximum scale of anumeric column can be configured. Todeclare a column of type numeric usethe syntaxNUMERIC( precision, scale)The precision must be positive, the scale zero orpositive. Alternatively,NUMERIC( precision)selects a scale of 0. SpecifyingNUMERICwithout any precision or scale creates a column in whichnumeric values of any precision and scale can be stored, upto the implementation limit on precision. A column of thiskind will not coerce input values to any particular scale,whereas numeric columns with a declaredscale will coerce input values to that scale. (TheSQL standard requires adefault scale of 0, i.e., coercion to integer precision.

Wefind this a bit useless. If you're concerned aboutportability, always specify the precision and scaleexplicitly.)If the scale of a value to be stored is greater than thedeclared scale of the column, the system will round the valueto the specified number of fractional digits. Then, if thenumber of digits to the left of the decimal point exceeds thedeclared precision minus the declared scale, an error israised.Numeric values are physically stored without any extraleading or trailing zeroes.

Thus, the declared precision andscale of a column are maximums, not fixed allocations. (Inthis sense the numeric type is moreakin to varchar( n) than to char( n).) Theactual storage requirement is two bytes for each group offour decimal digits, plus eight bytes overhead.In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning 'not-a-number'.

Any operation on NaN yields another NaN. When writing this value as a constant ina SQL command, you must put quotes around it, for exampleUPDATE table SET x = 'NaN'. Oninput, the string NaN is recognizedin a case-insensitive manner.The types decimal and numeric are equivalent.

Both types are part ofthe SQL standard. The data types real and double precision are inexact, variable-precisionnumeric types. In practice, these types are usuallyimplementations of IEEEStandard 754 for Binary Floating-Point Arithmetic (single anddouble precision, respectively), to the extent that theunderlying processor, operating system, and compiler supportit.Inexact means that some values cannot be converted exactlyto the internal format and are stored as approximations, sothat storing and printing back out a value may show slightdiscrepancies. The data types serial and bigserial are not true types, but merely anotational convenience for setting up unique identifiercolumns (similar to the AUTOINCREMENT property supported by someother databases). In the current implementation,specifyingCREATE TABLE tablename (colname SERIAL);is equivalent to specifying:CREATE SEQUENCE tablename colnameseq;CREATE TABLE tablename (colname integer DEFAULT nextval(' tablename colnameseq') NOT NULL);Thus, we have created an integer column and arranged forits default values to be assigned from a sequence generator.A NOT NULL constraint is applied toensure that a null value cannot be explicitly inserted,either.

In most cases you would also want to attach aUNIQUE or PRIMARY KEY constraint to prevent duplicatevalues from being inserted by accident, but this is notautomatic. Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. Ifyou wish a serial column to be in a unique constraint ora primary key, it must now be specified, same as with anyother data type.To insert the next value of the sequence into theserial column, specify that theserial column should be assigned itsdefault value. This can be done either by excluding thecolumn from the list of columns in the INSERT statement, or through the use of theDEFAULT key word.The type names serial and serial4 are equivalent: both create integer columns.

The type names bigserial and serial8 workjust the same way, except that they create a bigint column. Bigserialshould be used if you anticipate the use of more than2 31 identifiers over the lifetime of thetable.The sequence created for a serialcolumn is automatically dropped when the owning column isdropped, and cannot be dropped otherwise. (This was not truein PostgreSQL releasesbefore 7.3.