Tipos de Datos PostgreSQL

Tipos Numéricos

Tipo	Espacio de Almacenamiento	Descripción	Rango
`smallint`	2 bytes	small-range integer	-32,768 to +32,767
`integer`	4 bytes	typical choice for integer	-2,147’483,648 to +2,147’483,647
`bigint`	8 bytes	large-range integer	-9’223,372’036,854’775,808 to +9’223,372’036,854’775,807
`decimal`	variable	user-specified precision, exact	up to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point
`numeric`	variable	user-specified precision, exact	up to 131,072 digits before the decimal point; up to 16,383 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 32,767
`serial`	4 bytes	autoincrementing integer	1 to 2,147’483,647
`bigserial`	8 bytes	large autoincrementing integer	1 to 9’223,372’036,854’775,807

NUMERIC(precision, scale)
NUMERIC(3, 1) -- -99.9 and 99.9, inclusive
'Infinity' or 'inf'
'-Infinity' or '-inf'
'NaN'

Tipos Moneda

Tipo	Espacio de Almacenamiento	Descripción	Rango
`money`	8 bytes	currency amount	-92,233’720,368’547,758.08 to +92,233’720,368’547,758.07

Tipos Caracter

Tipo	Descripción
`character varying(n)`, `varchar(n)`	variable-length with limit
`character(n)`, `char(n)`, `bpchar(n)`	fixed-length, blank-padded
`bpchar`	variable unlimited length, blank-trimmed
`text`	variable unlimited length

Tipos Binarios

Tipo	Espacio de Almacenamiento	Descripción
`bytea`	1 or 4 bytes plus the actual binary string	variable-length binary string

Una cadena binaria es una secuencia de octetos (o bytes). Las cadenas binarias permiten almacenar octetos de valor cero y otros octetos “no imprimibles” (generalmente, octetos fuera del rango decimal 32 a 126). Las cadenas de caracteres no permiten octetos cero ni otros valores de octeto y secuencias de valores de octeto que no sean válidos de acuerdo con la codificación de conjunto de caracteres seleccionada de la base de datos. Las operaciones en cadenas binarias procesan los bytes reales, mientras que el procesamiento de cadenas de caracteres depende de la configuración local. En resumen, las cadenas binarias son apropiadas para almacenar datos que el programador considera “bytes crudos”, mientras que las cadenas de caracteres son apropiadas para almacenar texto.

SET bytea_output = 'hex';

SELECT '\xDEADBEEF'::bytea;
   bytea
------------
 \xdeadbeef

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T

Valor Decimal del Octeto	Descripción	Representación de Entrada Escapada	Ejemplo	Representación Hex
0	zero octet	`'\000'`	`'\000'::bytea`	`\x00`
39	single quote	`''''` or `'\047'`	`''''::bytea`	`\x27`
92	backslash	`'\\'` or `'\134'`	`'\\'::bytea`	`\x5c`
0 to 31 and 127 to 255	“non-printable” octets	`'\xxx'` (octal value)	`'\001'::bytea`	`\x01`

Tipos Fecha / Hora

Tipo	Espacio de Almacenamiento	Descripción	Valor Minimo	Valor Máximo	Resolución
`timestamp [ (p) ] [ without time zone ]`	8 bytes	both date and time (no time zone)	4713 BC	294276 AD	1 microsecond
`timestamp [ (p) ] with time zone`	8 bytes	both date and time, with time zone	4713 BC	294276 AD	1 microsecond
`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
`time [ (p) ] with time zone`	12 bytes	time of day (no date), with time zone	00:00:00+1559	24:00:00-1559	1 microsecond
`interval [ fields ] [ (p) ]`	16 bytes	time interval	-178000000 years	178000000 years	1 microsecond

Tipos Booleanos

Tipo	Tamaño de Almacenamiento	Descripción
`boolean`	1 byte	state of true or false

-- “true” state:
true
yes
on
1

-- “false” state:
false
no
off
0

Tipos Enumerados

-- 1. Crear la enumeración
-- 2. Utilizar la enumeración

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

Tipos Geométricos

Tipo	Esopacio de Almacenamiento	Descripción	Representación
`point`	16 bytes	Point on a plane	(x,y)
`line`	24 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)

-- Puntos
( x , y )
  x , y

-- Lineas:  Ax + By + C = 0
{ A, B, C }
[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

-- Segmentos de Línea
[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

-- Cajas / Rectangulos
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

-- Rutas
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

-- Poligonos
( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

-- Circulos
&lt; ( x , y ) , r >
( ( x , y ) , r )
  ( x , y ) , r
    x , y   , r

Tipos Dirección de Red (Network Address)

Tipo	Espacio de Almacenamiento	Descripción
`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
`macaddr8`	8 bytes	MAC addresses (EUI-64 format)

Tipos Cadenas de Bits

Tipos: bit(n) y bit varying(n)

CREATE TABLE test (a BIT(3), b BIT VARYING(5));

INSERT INTO test VALUES (B'101', B'00');

INSERT INTO test VALUES (B'10', B'101');
ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');

SELECT * FROM test;

  a  |  b
-----+-----
 101 | 00
 100 | 101

Tipos para Búsqueda de Texto

https://www.postgresql.org/docs/current/datatype-textsearch.html

Tipos UUID

https://www.postgresql.org/docs/current/datatype-uuid.html

Tipos XML

https://www.postgresql.org/docs/current/datatype-xml.html

Tipo JSON

https://www.postgresql.org/docs/current/datatype-json.html

Tipo Arreglo

https://www.postgresql.org/docs/current/arrays.html

Tipos Compuestos

https://www.postgresql.org/docs/current/rowtypes.html

Tipos Rango

https://www.postgresql.org/docs/current/rangetypes.html

Dominios de Tipos de Dato

https://www.postgresql.org/docs/current/domains.html

Tipos Identificador de Objeto

Tipo	Referencia a:	Descripción	Ejemplo
`oid`	any	numeric object identifier	`564182`
`regclass`	`pg_class`	relation name	`pg_type`
`regcollation`	`pg_collation`	collation name	`"POSIX"`
`regconfig`	`pg_ts_config`	text search configuration	`english`
`regdictionary`	`pg_ts_dict`	text search dictionary	`simple`
`regnamespace`	`pg_namespace`	namespace name	`pg_catalog`
`regoper`	`pg_operator`	operator name	`+`
`regoperator`	`pg_operator`	operator with argument types	`*(integer,integer)` or `-(NONE,integer)`
`regproc`	`pg_proc`	function name	`sum`
`regprocedure`	`pg_proc`	function with argument types	`sum(int4)`
`regrole`	`pg_authid`	role name	`smithee`
`regtype`	`pg_type`	data type name	`integer`

https://www.postgresql.org/docs/current/datatype-oid.html

Pseudotipos

Tipo	Descripción
`any`	Indicates that a function accepts any input data type.
`anyelement`	Indicates that a function accepts any data type (see Section 36.2.5).
`anyarray`	Indicates that a function accepts any array data type (see Section 36.2.5).
`anynonarray`	Indicates that a function accepts any non-array data type (see Section 36.2.5).
`anyenum`	Indicates that a function accepts any enum data type (see Section 36.2.5 and Section 8.7).
`anyrange`	Indicates that a function accepts any range data type (see Section 36.2.5 and Section 8.17).
`anymultirange`	Indicates that a function accepts any multirange data type (see Section 36.2.5 and Section 8.17).
`anycompatible`	Indicates that a function accepts any data type, with automatic promotion of multiple arguments to a common data type (see Section 36.2.5).
`anycompatiblearray`	Indicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type (see Section 36.2.5).
`anycompatiblenonarray`	Indicates that a function accepts any non-array data type, with automatic promotion of multiple arguments to a common data type (see Section 36.2.5).
`anycompatiblerange`	Indicates that a function accepts any range data type, with automatic promotion of multiple arguments to a common data type (see Section 36.2.5 and Section 8.17).
`anycompatiblemultirange`	Indicates that a function accepts any multirange data type, with automatic promotion of multiple arguments to a common data type (see Section 36.2.5 and Section 8.17).
`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`.
`table_am_handler`	A table access method handler is declared to return `table_am_handler`.
`index_am_handler`	An index access method handler is declared to return `index_am_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.
`unknown`	Identifies a not-yet-resolved type, e.g., of an undecorated string literal.

Make your ideas shine!

Destacados!!!

Manual Pasaporte TICs 2026 (UTVAM)