PostgreSQL Data Types
- This chapter discusses PostgreSQL Data Types While creating table, for each column, you specify a data type, i.e., what kind of data you want to store in the table fields.
- There are different categories of data types in PostgreSQL. They are discussed as below:
Numeric datatype:
- Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals.
Name | Storage Size | Description | Range |
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807 |
decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
Double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
- The money type stores a currency amount with a fixed fractional precision;
- The fractional precision is determined by the database's lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale.
Name | Storage Size | Description | Range |
money | 8 bytes | currency amount | -92233720368547758.08 to +92233720368547758.07 |
- The table below lists general-purpose character types available in PostgreSQL.
Name | Description |
character varying(n), varchar(n) | variable-length with limit |
character(n), char(n) | fixed-length, blank padded |
text | variable unlimited length |
- The bytea data type allows storage of binary strings
Name | Storage Size | Description |
bytea | 1 or 4 bytes plus the actual binary string | variable-length binary string |
- PostgreSQL supports the full set of SQL date and time types,
- Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced
Name | Storage Size | Description | Low Value | High Value | Resolution |
timestamp [ (p) ] [ without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond / 14 digits |
timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond / 14 digits |
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ] [ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond / 14 digits |
time [ (p) ] with time zone | 12 bytes | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 | 1 microsecond / 14 digits |
interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond / 14 digits |
Boolean Type:
- PostgreSQL provides the standard SQL type boolean;
- The boolean type can have several states: "true", "false", and a third state, "unknown", which is represented by the SQL null value.
Name | Storage Size | Description |
boolean | 1 byte | state of true or false |
Valid literal values for the "true" state are:
TRUE
't'
'true'
'y'
'yes'
'on'
'1'
For the "false" state, the following values can be used:
FALSE
'f'
'false'
'n'
'no'
'off'
'0'
Leading or trailing whitespace is ignored, and case does not matter. The key words TRUE and FALSE are the preferred (SQL-compliant) usage.
- Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages.
- An example of an enum type might be the days of the week, or a set of status values for a piece of data.
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe | happy
(1 row)
Geometric Types:- Geometric data types represent two-dimensional spatial objects
- A rich set of functions and operators is available to perform various geometric operations such as scaling, translation, rotation, and determining intersections
Name | Storage Size | Description | Representation |
point | 16 bytes | Point on a plane | (x,y) |
line | 32 bytes | Infinite line | {A,B,C} |
lseg | 32 bytes | Finite line segment | ((x1,y1),(x2,y2)) |
box | 32 bytes | Rectangular box | ((x1,y1),(x2,y2)) |
path | 16+16n bytes | Closed path (similar to polygon) | ((x1,y1),...) |
path | 16+16n bytes | Open path | [(x1,y1),...] |
polygon | 40+16n bytes | Polygon (similar to closed path) | ((x1,y1),...) |
circle | 24 bytes | Circle | <(x,y),r> (center point and radius) |
- PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses.
- It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions
Name | Storage Size | Description |
cidr | 7 or 19 bytes | IPv4 and IPv6 networks |
inet | 7 or 19 bytes | IPv4 and IPv6 hosts and networks |
macaddr | 6 bytes | MAC addresses |
Bit String Type:
- Bit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.
- PostgreSQL provides two data types that are designed to support full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query.
- The tsvector type represents a document in a form optimized for text search;
- the tsquery type similarly represents a text query.
- A UUID (Universally Unique Identifiers) is written as a sequence of lower-case hexadecimal digits,
- In several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.
XML Types
- The xml data type can be used to store XML data. Its advantage over storing XML data in a text field is that it checks the input values for well-formedness,
- There are support functions to perform type-safe operations on it;.
- Use of this data type requires the installation to have been built with configure --with-libxml.
- JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159.
- Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules.
- There are also assorted JSON-specific functions and operators available for data stored in these data types
JSON primitive types and corresponding PostgreSQL types
JSON primitive type | PostgreSQL type | Notes |
string | text | \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8 |
number | numeric | NaN and infinity values are disallowed |
boolean | boolean | Only lowercase true and false spellings are accepted |
null | (none) | SQL NULL is a different concept |
Arrays Types:
- PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported.
Composite Types:
- A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.
- PostgreSQL allows composite types to be used in many of the same ways that simple types can be used.
- For example, a column of a table can be declared to be of a composite type.
Range Types:
- Range types are data types representing a range of values of some element type (called the range's subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for "timestamp range"), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.
- Range types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly.
- The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.
PostgreSQL comes with the following built-in range types:
int4range — Range of integer
int8range — Range of bigint
numrange — Range of numeric
tsrange — Range of timestamp without time zone
tstzrange — Range of timestamp with time zone
daterange — Range of date
Object Identifier Types:
- Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.
- OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created, or the default_with_oids configuration variable is enabled.
- Type oid represents an object identifier. There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary
pg_lsn Type:
- The pg_lsn data type can be used to store LSN (Log Sequence Number) data which is a pointer to a location in the XLOG.
- This type is a representation of XLogRecPtr and an internal system type of PostgreSQL.
- Internally, an LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. The pg_lsn type supports the standard comparison operators, like = and >. Two LSNs can be subtracted using the - operator; the result is the number of bytes separating those write-ahead log positions.
Pseudo-Types:
- The PostgreSQL type system contains a number of special-purpose entries that are collectively called pseudo-types.
- A pseudo-type cannot be used as a column data type, but it can be used to declare a function's argument or result type. Each of the available pseudo-types is useful in situations where a function's behavior does not correspond to simply taking or returning a value of a specific SQL data type
Name | Description |
any | Indicates that a function accepts any input data type. |
anyelement | Indicates that a function accepts any data type |
anyarray | Indicates that a function accepts any array data type |
anynonarray | Indicates that a function accepts any non-array data type |
anyenum | Indicates that a function accepts any enum data type |
anyrange | Indicates that a function accepts any range data type |
cstring | Indicates that a function accepts or returns a null-terminated C string. |
internal | Indicates that a function accepts or returns a server-internal data type. |
language_handler | A procedural language call handler is declared to return language_handler. |
fdw_handler | A foreign-data wrapper handler is declared to return fdw_handler. |
tsm_handler | A tablesample method handler is declared to return tsm_handler. |
record | Identifies a function taking or returning an unspecified row type. |
trigger | A trigger function is declared to return trigger. |
event_trigger | An event trigger function is declared to return event_trigger. |
pg_ddl_command | Identifies a representation of DDL commands that is available to event triggers. |
void | Indicates that a function returns no value. |
opaque | An obsolete type name that formerly served all the above purposes. |
Postgres Domain Data Type:
- user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint.
- domain will be check faster than primary key you will understand from Domain Data Type Example
Comments
Post a Comment