# Databases : PostgreSQL : Types ## Build-In General Purpose Data types > [!NOTE] > Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. | Name | Alias | Description | | -------------------------------- | ------------------ | --------------------------------------------------- | | bigint | int8 | signed eight-byte integer | | bigserial | serial8 | autoincrementing eight-byte integer | | bit [( n )] | | fixed-length bit string (without n -> bit(1)) | | bit varying [( n )] | varbit [( n )] | variable-length bit string (without n -> unlimited) | | boolean | bool | logical Boolean (true/false) | | box | | rectangular box on a plane | | bpchar [( n )] | | variable-length character string | | bytea | | binary data (“byte array”) | | character [( n )] | char [( n )] | fixed-length character string | | character varying [( n )] | varchar [( n )] | variable-length character string | | cidr | | IPv4 or IPv6 network address | | circle | | circle on a plane | | date | | calendar date (year, month, day) | | double precision | float8 | double precision floating-point number (8 bytes) | | inet | | IPv4 or IPv6 host address | | integer | int, int4 | signed four-byte integer | | interval [ fields ] [( p )] | | time span | | json | | textual JSON data | | jsonb | | binary JSON data, decomposed | | line | | infinite line on a plane | | lseg | | line segment on a plane | | macaddr | | MAC (Media Access Control) address | | macaddr8 | | MAC (Media Access Control) address (EUI-64 format) | | money | | currency amount | | numeric [( p, s )] | decimal [( p, s )] | exact numeric of selectable precision | | path | | geometric path on a plane | | pg_lsn | | PostgreSQL Log Sequence Number | | pg_snapshot | | user-level transaction ID snapshot | | point | | geometric point on a plane | | polygon | | closed geometric path on a plane | | real | float4 | single precision floating-point number (4 bytes) | | smallint | int2 | signed two-byte integer | | smallserial | serial2 | autoincrementing two-byte integer | | serial | serial4 | autoincrementing four-byte integer | | text | | variable-length character string | | time [( p )] | | time of day (no time zone) | | time [( p )] with time zone | timetz | time of day, including time zone | | timestamp [( p )] | | date and time (no time zone) | | timestamp [( p )] with time zone | timestamptz | date and time, including time zone | | tsquery | | text search query | | tsvector | | text search document | | uuid | | universally unique identifier | | xml | | XML data | ## By Group #### Textual ```sql char( n ) -- fixed-length, padded to n varchar( n ) -- variable-length, capped at n text -- variable length, up to ~1gb ``` - cannot hold code 0 aka NUL - `n` must be <= 10,485,760 - if `n` not specified for... - `char` -> equivalent to `char( 1 )` - `varchar` -> equivalent to `text` - **no performance difference between types** so just use `text` unless you're certain about max length and want to enforce it ("state char( 2 )") - `text` is PostgreSQL's native string data type, in that most built-in functions operating on strings are declared to take or return `text`, not `varchar` - for many purposes, `varchar` acts as though it were a domain over `text` - long strings are compressed by the system automatically, so the physical requirement on disk might be less - re: `char` padding... - trailing spaces are treated as semantically insignificant and disregarded when comparing two `char` values - trailing spaces are removed when converting char to another string type #### Numeric ```sql smallint -- 2b -32768..32767 integer -- 4b -2147483648..2147483647 bigint -- 8b -9223372036854775808..9223372036854775807 smallserial -- 2b 1..32767 serial -- 4b 1..2147483647 bigserial -- 8b 1..9223372036854775807 -- fixed-point aka arbitrary precision -- precision = total digits (not including decimal), scale = digits to the right of decimal numeric | decimal [( precision, scale )] -- if scale omitted, defaults to 0 (basically an integer) -- if both omitted, defaults to max precision (147455.16383) -- if not omitted, called a "constrained numer" and limited to ( 1000, 1000 ) money -- 8b -92233720368547758.08 to +92233720368547758.07 -- floating-point real -- 4b range 1E-37 to 1E+37 with 6 precision double precision -- 8b range 1E-307 to 1E+308 with 15 precision ``` **Money** - Values of the `numeric`, `int`, and `bigint` data types can be cast to money. - Conversion from the `real` and `double precision` data types can be done by casting to numeric first: `<float>::numeric::money` - Division of a money value by an integer value is performed with truncation of the fractional part towards zero. - To get a rounded result, divide by a floating-point value, or cast the `money` value to `numeric` before dividing and back to `money` afterwards. (The latter is preferable to avoid risking precision loss.) - When a `money` value is divided by another `money` value, the result is `double precision` (i.e., a pure number, not `money`); the currency units cancel each other out in the division. **Serial Sequences** ```sql CREATE TABLE tablename ( colname SERIAL ) -- ...is equivalent to... CREATE SEQUENCE tablename_colname_seq AS integer CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval( 'tablename_colname_seq' ) ) ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname ``` #### Chronologic ```sql timestamptz | timestamp with time zone timestamp 8b 4713 BC .. 294276 AD date 4b 4713 BC .. 5874897 AD time 8b 00:00:00 .. 24:00:00 interval 16b +/- 178,000,000 years # holds a unit (microsecond..millenium) and quantity - 12 days or 8 hours can specify timezone as "EST", "-8" (UTC offest), or "Australia/Melbourne" can specify intervals like '2 days' or '1 century' ("1 week" might be displayed as "7 days", depending on PG config) function: now() ``` #### Geometric Geometric data types represent spatial objects on a cartesian plane. | Name | Bytes | Description | Representation | | ------- | ------ | -------------------------------- | ---------------------------- | | point | 16 | point on a plane | `( x, y )` | | line | 24 | infinite line | `{ A, B, C }` | | lseg | 32 | finite line segment | `( ( x1, y1 ), ( x2, y2 ) )` | | box | 32 | rectangular box | `( ( x1, y1 ), ( x2, y2 ) )` | | path | 16+16n | closed path (similar to polygon) | `( ( x1, y1 ), ... )` | | path | 16+16n | open path | `[ ( x1, y1 ), ... ]` | | polygon | 40+16n | polygon (similar to closed path) | `( ( x1, y1 ), ... )` | | circle | 24 | circle | `< ( x, y ), r >` | In all these types, the individual coordinates are stored as `double precision` (float8) numbers. A rich set of functions and operators is available to perform various geometric operations such as scaling, translation, rotation, and determining intersections. ## Enum A static, ordered set of values. ```sql CREATE TYPE mood AS ENUM ( 'sad', 'ok', 'happy' ) -- case-sensitive ``` Once created, the enum type can be used in table and function definitions much like any other type: ```sql CREATE TABLE person ( name text, current_mood mood ) INSERT INTO person VALUES ( 'Moe', 'happy' ) SELECT * FROM person WHERE current_mood >= 'ok' ORDER BY current_mood ALTER TYPE mood ... -- to add or rename values ``` > [!NOTE] > Existing values cannot be removed from an `enum` type, nor can the sort ordering of such values be changed, short of dropping and re-creating the `enum` type. The translations from internal `enum` values to textual labels are kept in the system catalog `pg_enum`. ## Casting ```sql <type> 'value' -- doesn't work for array types 'value'::<type> -- NULL::boolean, 1.23::money CAST ( 'value' AS <type> ) ``` Casting a string to `char( n )` or `varchar( n )` will truncate the string to `n`. ## Custom Types Users can add new types to PostgreSQL using the `CREATE TYPE` command. #### Domains A domain is a user-defined data type that is based on another underlying type. Optionally, it can have constraints that restrict its valid values. ```sql CREATE DOMAIN posint AS integer CHECK ( VALUE > 0 ) ``` When an operator or function of the underlying type is applied to a domain value, the domain is automatically down-cast to the underlying type.