PostgreSQL provides a rich collection of native data types for storing numbers, text, dates, timestamps, binary data, structured documents, network addresses, geometric information and more. Choosing the correct data type is important because it affects storage requirements, precision, validation, indexing, query performance and application behavior.

This reference covers PostgreSQL's built-in data types, their aliases, practical examples, recommended use cases, situations to avoid and better alternatives where applicable.

1. Numeric Data Types

Numeric types store whole numbers, decimal numbers and approximate floating-point values. Choosing between them depends on the required range, precision and storage efficiency.

Data Type	Alias	Storage	Value Range / Precision	Example Value	When to Use	When NOT to Use	Recommended Alternative
smallint	int2	2 bytes	-32,768 to 32,767	120	Small integer values such as ratings, quantities and status codes.	When values can exceed 32,767.	integer for a larger range.
integer	int, int4	4 bytes	-2,147,483,648 to 2,147,483,647	250000	Default choice for most whole-number values, IDs within range, counts and quantities.	When values may exceed approximately 2.1 billion or require fractional precision.	bigint for larger integers; numeric for exact decimals.
bigint	int8	8 bytes	-9.22 × 10^18 to 9.22 × 10^18 approximately	9000000000	Large counters, high-volume transaction IDs and large integer values.	For small values where integer is sufficient and storage matters.	integer for smaller ranges; numeric for values beyond its range.
numeric(p,s)	decimal(p,s)	Variable	Exact decimal; up to 1,000 digits of precision in PostgreSQL 18	numeric(12,2) → 125000.75	Financial calculations, account balances, taxes, percentages and measurements requiring exact decimal representation.	For high-volume scientific calculations where approximate floating-point arithmetic is acceptable and performance matters more.	real or double precision for approximate calculations.
real	float4	4 bytes	Approximately 6 decimal digits of precision	3.14159	Scientific measurements and approximate calculations where lower precision is acceptable.	Financial calculations, exact equality comparisons and values requiring high precision.	double precision for greater approximate precision; numeric for exact decimal arithmetic.
double precision	float8, float	8 bytes	Approximately 15 decimal digits of precision	3.141592653589793	Scientific computing, statistical calculations and approximate measurements.	Currency, exact decimal calculations and strict equality requirements.	numeric for exact arithmetic; real when lower precision is sufficient.
smallserial	serial2	2-byte integer + sequence	1 to 32,767	Automatically generated: 1, 2, 3	Small auto-generated identifiers with a known limited row count.	Large tables or systems that may exceed 32,767 rows.	serial or bigserial; preferably identity columns.
serial	serial4	4-byte integer + sequence	1 to 2,147,483,647	Automatically generated: 1, 2, 3	Traditional auto-incrementing integer primary keys.	When explicit sequence control or SQL-standard identity behavior is preferred.	integer GENERATED ALWAYS AS IDENTITY.
bigserial	serial8	8-byte integer + sequence	1 to 9.22 × 10^18 approximately	Automatically generated: 10000000001	Large auto-generated identifiers and high-volume tables.	When an ordinary integer identity is sufficient.	bigint GENERATED ALWAYS AS IDENTITY.

Understanding SERIAL and IDENTITY

serial, smallserial and bigserial are PostgreSQL-specific convenience constructs, not independent integer storage types. They create an integer column, an associated sequence and a default value using that sequence.

For new applications, identity columns are generally preferable because they use SQL-standard syntax and provide clearer control over automatic value generation.

Example:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

Use GENERATED BY DEFAULT AS IDENTITY when explicit values should also be permitted during inserts.

2. Monetary Data Type

Data Type	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
money	8 bytes	'$125.50'::money	Applications requiring PostgreSQL's locale-aware monetary formatting and operations.	Financial applications requiring portable, explicit decimal precision, multiple currencies or predictable formatting across environments.	numeric(12,2) with a separate currency code such as INR, USD or EUR.

Important: money depends on locale settings for input and output. For most production financial systems, numeric combined with an explicit currency column is the safer choice.

3. Character and String Data Types

Character types store text, names, descriptions, identifiers and other textual information.

Data Type	Alias	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
char(n)	character(n)	Fixed-length, blank-padded	char(5) → 'HELLO'	Fixed-width codes or identifiers that genuinely require fixed-length semantics.	Normal names, descriptions, emails and variable-length text.	varchar or text.
varchar(n)	character varying(n)	Variable-length, up to declared character limit	varchar(100) → 'Suriya'	Names, email addresses, titles and text with a meaningful maximum length.	When the limit has no business meaning and introduces unnecessary maintenance.	text when no database-level length limit is required.
varchar	character varying	Variable-length	'PostgreSQL Developer'	Variable-length text without a declared maximum.	When a fixed maximum length is a mandatory business rule.	varchar(n) with an appropriate limit or CHECK constraint.
text	None	Variable-length	'Learning PostgreSQL Data Types'	Descriptions, comments, articles, logs, messages and general text.	When a strict maximum length must be enforced directly by the column definition.	varchar(n) or text with a CHECK constraint.

CHAR vs VARCHAR vs TEXT

• char(n) always uses fixed-length, blank-padded semantics.
• varchar(n) enforces a maximum number of characters.
• text stores variable-length text without an explicit character limit.
• varchar without a length limit behaves like text for practical purposes.
• PostgreSQL does not provide a general performance advantage for varchar(n) over text.

For most application development, text is an excellent default. Use varchar(n) when the maximum length is an actual business requirement.

Example:

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    bio TEXT,
    country_code CHAR(2)
);

4. Date and Time Data Types

PostgreSQL offers several date and time types. The most important distinction is whether a value represents a calendar date, a local clock time, a timestamp or an absolute point in time.

Data Type	Alias	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
date	None	4 bytes	'2026-11-12'	Birthdays, joining dates, holidays and invoice dates where time is irrelevant.	When the exact time of an event matters.	timestamp or timestamptz.
time	time without time zone	8 bytes	'14:30:00'	Store opening hours, recurring daily schedules and local clock times.	Events that need an associated date or a globally unambiguous moment.	timestamp or timestamptz.
time(p)	time without time zone with precision	8 bytes	'14:30:25.123'	Time-of-day values requiring fractional-second precision.	Time values that need timezone-aware event interpretation.	timetz only when a time-of-day timezone offset is specifically needed; otherwise use timestamptz with a date.
timetz	time with time zone	12 bytes	'14:30:00+05:30'	Specialized situations where a time of day and UTC offset must be stored together.	Normal application timestamps and future appointments.	timestamptz for actual moments in time.
timestamp	timestamp without time zone	8 bytes	'2026-11-12 14:30:00'	Local date-time values such as a wall-clock appointment or a scheduled local business event.	When the timestamp represents an absolute moment across time zones.	timestamptz.
timestamptz	timestamp with time zone	8 bytes	'2026-11-12 14:30:00+05:30'	Audit timestamps, transaction times, API event times, login times and globally meaningful moments.	When you need to preserve the original timezone name or offset as entered.	timestamp plus a separate timezone identifier when local-time intent must be preserved.
interval	None	16 bytes	'2 days 03:30:00'	Durations, time differences, subscription periods and scheduling calculations.	When you need a fixed integer count of seconds or a specific calendar date.	bigint for a fixed number of seconds or milliseconds; date for calendar dates.

Understanding TIMESTAMP vs TIMESTAMPTZ

This is one of the most important PostgreSQL distinctions.

timestamp without time zone stores the date and clock time without timezone conversion.

timestamptz represents an absolute point in time. PostgreSQL converts input values to UTC internally and displays them according to the session timezone.

Example:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    scheduled_at TIMESTAMP,
    delivery_date DATE
);

For an application serving users across different countries, use timestamptz for actual events. Store an IANA timezone identifier such as Asia/Kolkata separately when the user's intended local timezone must be preserved.

Date/Time Precision

PostgreSQL supports fractional-second precision from 0 to 6 digits using timestamp(p), time(p) and interval(p).

For example:

• timestamp(0) → seconds only.
• timestamp(3) → milliseconds.
• timestamp(6) → microseconds.

5. Boolean Data Type

Data Type	Alias	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
boolean	bool	1 byte	TRUE, FALSE, NULL	Active/inactive flags, verification status, feature flags and yes/no attributes.	When the attribute has more than two meaningful states.	ENUM, lookup table or constrained text.

PostgreSQL accepts several input representations:

• True: TRUE, 't', 'yes', 'on', '1'.
• False: FALSE, 'f', 'no', 'off', '0'.
• Unknown: NULL.

Remember that SQL Boolean values have three logical states: TRUE, FALSE and NULL (unknown).

6. Binary Data Type

Data Type	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
bytea	Variable-length binary data	decode('48656c6c6f', 'hex')	Small binary values, encrypted payloads, hashes, signatures and binary content that belongs directly in the database.	Large files, videos and documents when database growth, backups and transfer overhead become problematic.	Object storage such as S3-compatible storage, with the file location and metadata stored in PostgreSQL.

Example:

CREATE TABLE file_metadata (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename TEXT NOT NULL,
    file_content BYTEA
);

bytea stores raw binary data. PostgreSQL also supports large objects for specialized use cases involving large binary values.

7. UUID Data Type

Data Type	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
uuid	16 bytes	'550e8400-e29b-41d4-a716-446655440000'	Distributed systems, public-facing identifiers, independently generated IDs and systems where IDs should not reveal sequential record counts.	When a compact, sequential integer key is preferable and UUIDs provide no practical benefit.	bigint GENERATED ALWAYS AS IDENTITY.

Example:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE
);

PostgreSQL 18 supports UUID generation functions, including gen_random_uuid() and UUIDv7 generation through uuidv7().

UUID versions have different characteristics:

• UUIDv4: Randomly generated identifiers.
• UUIDv7: Time-ordered identifiers that can improve index locality compared with random UUIDv4 values.

UUIDs are identifiers, not security tokens. Do not rely on their unpredictability as an authorization mechanism.

8. JSON Data Types

PostgreSQL provides two primary JSON storage types.

Data Type	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
json	Textual JSON representation	'{"name":"Suriya","age":28}'::json	When preserving the original JSON text representation is important and JSON processing is relatively limited.	Frequent JSON querying, indexing and updates.	jsonb.
jsonb	Decomposed binary JSON representation	' {"name":"Suriya","age":28} '::jsonb	Flexible attributes, API payloads, metadata, document-like data and searchable semi-structured information.	Core relational attributes that require strong constraints, joins and frequent structured queries.	Normal relational columns or a combination of relational columns and jsonb.

JSON vs JSONB

Feature	JSON	JSONB
Storage	Textual representation	Decomposed binary representation
Preserves whitespace	Yes	No
Preserves object key order	Yes	No
Preserves duplicate object keys	Yes in the stored text	No; only the last value is retained
Input processing	Minimal validation and storage as text	Parses and decomposes input
Querying	Supports JSON operators	Supports JSON operators and additional indexing capabilities
Indexing	No direct JSON indexing equivalent to JSONB	Supports GIN indexes and other indexing strategies
General recommendation	Specialized text-preservation requirements	Default choice for queryable JSON documents

Example:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO products (name, attributes)
VALUES (
    'Laptop',
    '{"brand":"Dell","ram":16,"storage":"512GB"}'::jsonb
);

SELECT attributes ->> 'brand'
FROM products;

Additional JSON-Related Type

Data Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
jsonpath	'$.products[*].price'::jsonpath	Complex path-based JSON queries using SQL/JSON path expressions.	As a general-purpose JSON storage column.	jsonb for storage and JSON operators for simple access.

9. Enumerated Data Types

ENUM is a user-defined PostgreSQL type rather than a general-purpose built-in scalar type. It restricts a column to a predefined set of labels.

Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
User-defined ENUM	'pending', 'approved', 'rejected'	Small, relatively stable sets of predefined states such as order status or payment status.	Frequently changing values, user-configurable options or states requiring extensive metadata.	Lookup table with a foreign key or constrained text.

Example:

CREATE TYPE order_status AS ENUM (
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

ENUM values have an ordering determined by their declaration order. Existing values can be renamed or new values added, but removing or reordering values requires a more involved migration strategy.

10. Array Data Types

PostgreSQL supports arrays of built-in and user-defined data types. Arrays are not a separate scalar type; they are collections of values associated with an element type.

Data Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
integer[]	ARRAY[10,20,30]	A small collection of integer values belonging to one record, such as tags represented by IDs or numeric measurements.	When individual elements require independent relationships, constraints or frequent updates.	A separate child table with a foreign key.
text[]	ARRAY['Node.js','PostgreSQL']	Small lists of strings, labels and simple attributes.	Complex relationships or large searchable collections.	A normalized related table or jsonb for document-like structures.
uuid[]	ARRAY['550e8400-e29b-41d4-a716-446655440000'::uuid]	Small collections of UUID references when relational integrity is not required for each element.	When every referenced UUID must be enforced as a foreign key.	A junction table.
numeric[]	ARRAY[10.5,20.75,30.25]	Small collections of exact numeric measurements.	When measurements need independent metadata or relational querying.	A normalized child table.

Example:

CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[]
);

INSERT INTO articles (title, tags)
VALUES ('PostgreSQL Basics', ARRAY['SQL','Database','Backend']);

SELECT *
FROM articles
WHERE tags @> ARRAY['SQL'];

PostgreSQL supports arrays of most data types, including multidimensional arrays. Array dimensions are not enforced as declared bounds in the same way many developers expect; applications should validate the intended structure when necessary.

11. Range and Multirange Data Types

Range types represent continuous or discrete intervals of values. Multirange types represent collections of non-contiguous ranges.

Data Type	Example Value	Represents	When to Use	When NOT to Use	Recommended Alternative
int4range	'[10,20)'	Integer range	Integer-based intervals and allocation ranges.	Individual unrelated integer values.	integer or integer[].
int8range	'[1000000000,2000000000)'	Big integer range	Large integer intervals.	Ordinary individual numeric values.	bigint.
numrange	'[1.5,9.5)'	Numeric range	Exact decimal intervals and price bands.	Single numeric values.	numeric.
tsrange	'[2026-11-12 09:00,2026-11-12 17:00)'	Timestamp range without timezone	Local-time scheduling intervals.	Globally coordinated event times.	tstzrange.
tstzrange	'[2026-11-12 09:00+05:30,2026-11-12 17:00+05:30)'	Timestamp range with timezone	Reservations, booking windows and time-based availability.	Single timestamp values.	timestamptz.
daterange	'[2026-11-01,2026-12-01)'	Date range	Subscription periods, leave periods and reporting periods.	Individual calendar dates.	date.
int4multirange	'{[1,5),[10,15)}'	Multiple integer ranges	Multiple separate integer intervals.	A single continuous interval.	int4range.
int8multirange	'{[100,200),[500,600)}'	Multiple big integer ranges	Separate large integer intervals.	A single interval.	int8range.
nummultirange	'{[1.5,3.5),[8.0,10.0)}'	Multiple numeric ranges	Multiple separate numeric intervals.	One continuous numeric interval.	numrange.
tsmultirange	'{[2026-11-12 09:00,2026-11-12 10:00),[2026-11-12 14:00,2026-11-12 15:00)}'	Multiple timestamp ranges without timezone	Multiple local-time availability periods.	Globally coordinated time intervals.	tstzmultirange.
tstzmultirange	'{[2026-11-12 09:00+05:30,2026-11-12 10:00+05:30)}'	Multiple timezone-aware timestamp ranges	Multiple booking or availability windows.	A single timestamp or simple interval.	tstzrange.
datemultirange	'{[2026-11-01,2026-11-05),[2026-11-10,2026-11-15)}'	Multiple date ranges	Multiple separate leave or subscription periods.	One continuous date interval.	daterange.

Range Boundaries

• [: Inclusive lower or upper boundary.
• ]: Inclusive upper or lower boundary.
• (: Exclusive lower or upper boundary.
• ): Exclusive upper or lower boundary.

For example, [10,20) includes 10 but excludes 20.

Range types are especially useful with exclusion constraints to prevent overlapping reservations or bookings.

Example:

CREATE TABLE room_reservations (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reservation_period TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        reservation_period WITH &&
    )
);

12. Network Address Data Types

PostgreSQL provides specialized types for IPv4, IPv6 and hardware addresses.

Data Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
inet	'192.168.1.10'	Store individual IPv4/IPv6 host addresses, optionally with a network mask.	When storing arbitrary text that merely resembles an IP address.	text only when IP-specific validation and operations are unnecessary.
cidr	'192.168.1.0/24'	Store network addresses and subnets.	Individual host addresses where host bits must be preserved.	inet for host addresses.
macaddr	'08:00:2b:01:02:03'	Store six-byte MAC addresses.	General identifiers or arbitrary hexadecimal strings.	text only when MAC-specific validation is unnecessary.
macaddr8	'08:00:2b:01:02:03:04:05'	Store eight-byte EUI-64 hardware addresses.	Standard six-byte MAC addresses.	macaddr.

Example:

CREATE TABLE server_logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ip_address INET NOT NULL,
    network CIDR,
    mac_address MACADDR
);

inet and cidr support network-specific operators, subnet containment checks and efficient indexing.

13. Bit String Data Types

Bit strings store sequences of binary digits.

Data Type	Alias	Example Value	When to Use	When NOT to Use	Recommended Alternative
bit(n)	None	B'1010'	Fixed-length binary flags, masks and bit-level operations.	General text or binary files.	boolean, integer bit masks or bytea, depending on the requirement.
bit varying(n)	varbit(n)	B'10101101'	Variable-length binary sequences with a defined maximum.	Arbitrary binary payloads or ordinary textual values.	bytea.

Example:

CREATE TABLE permissions (
    id INTEGER PRIMARY KEY,
    permission_flags BIT(8)
);

INSERT INTO permissions VALUES (1, B'10101010');

14. Geometric Data Types

PostgreSQL provides built-in geometric types for representing points, lines, shapes and paths on a two-dimensional plane.

Data Type	Example Value	Represents	When to Use	When NOT to Use	Recommended Alternative
point	'(10,20)'	A point with X and Y coordinates.	Simple 2D coordinate calculations.	Geographic coordinates requiring spherical calculations and geographic reference systems.	PostGIS geometry or geography.
line	'{1,2,3}'	An infinite line.	Basic 2D geometric calculations.	Road networks or geographic line features.	PostGIS geometry.
lseg	'[(1,2),(5,6)]'	A finite line segment.	Simple 2D line-segment operations.	Real-world geographic route modeling.	PostGIS geometry(LineString).
box	'(5,5),(1,1)'	A rectangular box.	Simple rectangular geometric calculations.	Complex GIS spatial objects.	PostGIS geometry(Polygon).
path	'[(1,2),(3,4),(5,6)]'	An open or closed geometric path.	Simple 2D path calculations.	Production GIS routing and spatial analysis.	PostGIS geometry.
polygon	'((0,0),(0,5),(5,5),(5,0))'	A closed geometric shape.	Basic 2D polygon operations.	Geographic boundaries and advanced spatial operations.	PostGIS geometry(Polygon).
circle	'<(5,5),10>'	A circle represented by center and radius.	Basic geometric calculations.	Complex geographic or spatial modeling.	PostGIS geometry or separate numeric coordinates and radius.

Important: PostgreSQL's native geometric types operate in a simple two-dimensional coordinate plane. They are not a replacement for PostGIS when building real-world GIS applications.

15. Text Search Data Types

PostgreSQL includes built-in types that support full-text search.

Data Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
tsvector	to_tsvector('english', 'PostgreSQL is powerful')	Store normalized search documents for full-text search.	Ordinary application text storage.	text.
tsquery	to_tsquery('english', 'postgresql & database')	Represent full-text search expressions and matching conditions.	Storing user-facing search text or documents.	text for raw search input; tsquery for parsed search expressions.

Example:

CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    search_vector TSVECTOR
);

CREATE INDEX articles_search_idx
ON articles USING GIN (search_vector);

UPDATE articles
SET search_vector =
    to_tsvector('english', title || ' ' || body);

SELECT *
FROM articles
WHERE search_vector @@
    to_tsquery('english', 'postgresql & database');

16. XML Data Type

Data Type	Storage	Example Value	When to Use	When NOT to Use	Recommended Alternative
xml	Variable-length XML data	'<user><name>Suriya</name></user>'::xml	Systems integrating with XML-based enterprise applications, SOAP services and XML documents requiring XML-specific processing.	New applications using JSON-based APIs or ordinary relational data.	jsonb for JSON documents; normalized relational columns for structured application data.

Example:

CREATE TABLE xml_documents (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document XML NOT NULL
);

PostgreSQL supports XML processing functions and XPath-related operations.

17. Composite Data Types

Composite types group multiple fields into one structured value. PostgreSQL automatically creates a composite type for every table, and developers can also define their own composite types.

Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
User-defined composite type	ROW('Chennai', 'India')::address_type	Reusable structured values containing related attributes, function parameters and specialized database operations.	When each field requires independent indexing, constraints or relational relationships.	A normalized table or separate relational columns.

Example:

CREATE TYPE address_type AS (
    city TEXT,
    country TEXT
);

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    address address_type
);

18. Domain Data Types

A domain is a user-defined type based on an existing type with additional constraints. It allows reusable validation rules across multiple tables.

Type	Example Value	When to Use	When NOT to Use	Recommended Alternative
User-defined DOMAIN	'+919876543210'::phone_number	Reusable validation rules such as positive numbers, valid codes and constrained identifiers.	When validation differs significantly between columns or is highly context-dependent.	Base type with a column-level CHECK constraint.

Example:

CREATE DOMAIN positive_amount AS NUMERIC(12,2)
CHECK (VALUE > 0);

CREATE TABLE payments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount positive_amount NOT NULL
);

Domains can reduce duplicated validation rules and improve consistency across database schemas.

19. Object Identifier Types

PostgreSQL uses object identifier types internally to identify database objects and certain transaction-related entities. They are generally not intended for ordinary application data.

Data Type	Alias / Category	Example Value	Purpose	Application Recommendation
oid	Object identifier	12345	Identifies PostgreSQL catalog objects.	Do not use as a general application primary key. Use integer, bigint or uuid.
regclass	OID alias	'public.users'::regclass	References a relation such as a table or sequence by name.	Use actual relational foreign keys for application relationships.
regcollation	OID alias	'default'::regcollation	References a collation object.	Use normal text configuration for application settings.
regconfig	OID alias	'english'::regconfig	References a text-search configuration.	Use text-search configuration names when storing user preferences.
regdictionary	OID alias	'simple'::regdictionary	References a text-search dictionary.	Use normal configuration values in application tables.
regnamespace	OID alias	'public'::regnamespace	References a schema namespace.	Use schema metadata and normal identifiers for application logic.
regoper	OID alias	'+'::regoper	References an operator.	Internal database metadata use.
regoperator	OID alias	'+(integer,integer)'::regoperator	References an operator with argument types.	Internal database metadata use.
regproc	OID alias	'lower'::regproc	References a function by name where the name identifies it.	Use normal function names in application configuration.
regprocedure	OID alias	'lower(text)'::regprocedure	References a function or procedure with its argument signature.	Internal database metadata use.
regrole	OID alias	'postgres'::regrole	References a database role.	Use normal role names in application configuration.
regtype	OID alias	'integer'::regtype	References a PostgreSQL data type.	Use ordinary application-level type names.

These types are useful in system catalogs, database administration and dynamic SQL. They should not replace normal relational identifiers.

20. Transaction Identifier Types

These types represent internal transaction identifiers and snapshots.

Data Type	Example Value	Purpose	Application Recommendation
xid	12345	Represents a transaction ID.	Internal database operations; do not use as an application identifier.
xid8	123456789012	Represents an extended transaction ID.	Internal transaction-management operations.
cid	0	Represents a command identifier within a transaction.	Internal database operations.
pg_snapshot	'100:105:100,102'::pg_snapshot	Represents a transaction snapshot.	Specialized transaction visibility and monitoring operations.
txid_snapshot	Historical snapshot representation	Deprecated legacy snapshot type.	Prefer pg_snapshot.

Transaction identifiers are implementation-level concepts. Applications should not rely on them as stable business identifiers.

21. PostgreSQL Log Sequence Number Type

Data Type	Example Value	Purpose	When to Use	When NOT to Use	Recommended Alternative
pg_lsn	'0/16B6C50'::pg_lsn	Represents a Write-Ahead Log (WAL) log sequence number.	Replication monitoring, backup management and database administration.	General application identifiers, timestamps or counters.	bigint for ordinary numeric counters; timestamptz for event times.

pg_lsn represents a position in PostgreSQL's WAL stream. It is particularly useful when monitoring replication lag and recovery progress.

22. Pseudo-Types

Pseudo-types are special PostgreSQL types used in function declarations and internal database operations. They are not normal column types for application tables.

Pseudo-Type	Purpose	Example Usage	Application Recommendation
any	Generic polymorphic argument type.	Function arguments accepting compatible values.	Use only when creating specialized polymorphic functions.
anyelement	Represents an arbitrary data element type in legacy polymorphic function declarations.	Generic SQL functions.	Use modern polymorphic declarations where appropriate.
anyarray	Represents an array of an arbitrary element type in legacy polymorphic function declarations.	Generic array-processing functions.	Use only for polymorphic function definitions.
anynonarray	Represents a non-array type in legacy polymorphic declarations.	Generic function arguments.	Specialized function declarations only.
anyenum	Represents an arbitrary enum type in legacy polymorphic declarations.	Generic enum-processing functions.	Specialized function declarations only.
anyrange	Represents an arbitrary range type in legacy polymorphic declarations.	Generic range functions.	Specialized function declarations only.
anymultirange	Represents an arbitrary multirange type in legacy polymorphic declarations.	Generic multirange functions.	Specialized function declarations only.
anycompatible	Supports compatible polymorphic argument types.	Generic functions with compatible argument types.	Function definitions only.
anycompatiblearray	Compatible polymorphic array type.	Generic array-processing functions.	Function definitions only.
anycompatiblenonarray	Compatible non-array type.	Generic functions.	Function definitions only.
anycompatiblerange	Compatible range type.	Generic range functions.	Function definitions only.
anycompatiblemultirange	Compatible multirange type.	Generic multirange functions.	Function definitions only.
record	Represents an anonymous composite record.	Functions returning dynamically shaped records.	Use a defined composite type or table structure when the shape is known.
record[]	Array of record values.	Specialized record-processing functions.	Use normalized relational tables for persistent application data.
void	Represents a function that does not return a value.	RETURNS void	Not a table column type.
trigger	Represents a trigger function return type.	RETURNS trigger	Trigger function declarations only.
event_trigger	Represents an event trigger function return type.	RETURNS event_trigger	Event trigger declarations only.
internal	Internal PostgreSQL function argument or return representation.	System-level functions.	Never use as a regular application column type.
cstring	Null-terminated C string representation.	Internal function interfaces.	Use text for application strings.
language_handler	Represents a procedural language handler.	Language extension implementation.	Internal extension development only.
fdw_handler	Represents a foreign data wrapper handler.	Foreign data wrapper implementation.	Internal extension development only.
table_am_handler	Represents a table access method handler.	Custom table access method implementation.	Internal database extension development only.
index_am_handler	Represents an index access method handler.	Custom index access method implementation.	Internal database extension development only.
tsm_handler	Represents a table sampling method handler.	Custom table sampling implementation.	Internal extension development only.
unknown	Represents an unresolved type in certain SQL processing contexts.	Internal parsing and type resolution.	Use an explicit supported type in application SQL.

Important: Pseudo-types are primarily relevant to PostgreSQL function and extension development. They should not be confused with ordinary column types.

23. User-Defined Data Types

PostgreSQL allows developers to create custom data types using CREATE TYPE and related commands.

The major approaches include:

Type Category	Example	When to Use	When NOT to Use	Alternative
ENUM	CREATE TYPE status AS ENUM (...)	Small, stable sets of permitted values.	Frequently changing options.	Lookup table.
DOMAIN	CREATE DOMAIN positive_number AS INTEGER CHECK (VALUE > 0)	Reusable constraints over an existing type.	One-off column-specific validation.	Column-level constraints.
COMPOSITE	CREATE TYPE address AS (city TEXT, country TEXT)	Reusable structured values.	Complex relationships requiring independent records.	Relational tables.
RANGE	CREATE TYPE custom_range AS RANGE (subtype = numeric)	Custom interval semantics.	Ordinary individual values.	Existing built-in range types.
BASE TYPE	Custom C-language type implementation	Specialized database extensions and advanced database engineering.	Normal application development.	Built-in types or composite types.

24. Quick Decision Guide: Which Data Type Should You Choose?

Requirement	Recommended Type	Reason
Small whole number	smallint	Efficient 2-byte integer.
Normal whole number	integer	Suitable for most counts and quantities.
Large whole number	bigint	Supports a much larger integer range.
Auto-generated primary key	bigint GENERATED ALWAYS AS IDENTITY	Modern identity-based key generation.
Money and currency calculations	numeric(12,2)	Exact decimal arithmetic.
Approximate scientific calculation	double precision	Good balance of precision and performance.
Normal text	text	Flexible variable-length text.
Text with a business-defined maximum	varchar(n)	Enforces the maximum length.
Fixed-length code	char(n)	Fixed-width, blank-padded semantics.
Calendar date	date	Stores a date without a time.
Local clock time	time	Stores a time without a date.
Globally meaningful event time	timestamptz	Represents an absolute point in time.
Duration	interval	Represents time spans.
True/false attribute	boolean	Native Boolean semantics.
Binary payload	bytea	Stores binary data.
Globally unique identifier	uuid	Suitable for distributed identifier generation.
Flexible JSON document	jsonb	Queryable and indexable structured JSON.
Original JSON text preservation	json	Retains textual representation.
Fixed set of stable states	ENUM	Type-safe predefined labels.
Small collection of values	Array type	Stores collections in one column.
Prevent overlapping periods	Range type	Native interval operations and exclusion constraints.
IP address	inet	Validates and operates on IP addresses.
Network subnet	cidr	Represents network prefixes.
Hardware MAC address	macaddr	Native six-byte MAC representation.
Full-text search	tsvector and tsquery	Native full-text search functionality.
Geographic data	PostGIS geometry or geography	Advanced spatial calculations and indexing.

25. Common Data Type Selection Mistakes

1. Using FLOAT for currency: Floating-point values can introduce rounding errors. Prefer numeric for exact decimal calculations.
2. Using VARCHAR(255) everywhere: A length limit should represent a real business requirement. PostgreSQL text is often sufficient.
3. Using TIMESTAMP for globally meaningful events: A timestamp without timezone does not identify a globally unambiguous moment. Prefer timestamptz for events.
4. Using BIGINT for every integer: Choose the smallest type that comfortably satisfies the expected range and future requirements.
5. Using JSONB for the entire database schema: Frequently queried relational attributes should generally remain proper columns with constraints and indexes.
6. Using ARRAY instead of relational tables for complex relationships: Arrays cannot provide ordinary foreign-key constraints on individual elements.
7. Using MONEY without considering locale: Monetary formatting and input/output behavior depend on locale settings.
8. Using CHAR(n) for normal strings: Blank padding can create unexpected comparison and application behavior.
9. Using UUIDs as authorization tokens: Identifiers do not replace proper authentication and authorization mechanisms.
10. Using internal PostgreSQL types in business tables: Types such as oid, xid and pg_lsn have specialized database purposes.

26. Important Notes About Storage and Performance

• Integer types have fixed storage sizes: smallint uses 2 bytes, integer uses 4 bytes and bigint uses 8 bytes.
• numeric offers exact decimal representation but generally requires more processing and storage than integer or floating-point types.
• text and varchar use variable-length storage and can use TOAST for sufficiently large values.
• jsonb supports indexing through GIN and other index strategies, but indexes should be selected according to actual query patterns.
• uuid occupies 16 bytes before index overhead; random UUID insertion patterns can affect B-tree index locality.
• timestamptz stores an absolute instant, not the original timezone name.
• PostgreSQL arrays can contain multiple dimensions, but application-level validation may be necessary to enforce a specific structure.
• Data type choice also affects index size, sort operations, joins, comparison behavior and network transfer.

For production databases, choose types based on domain requirements, data integrity, expected volume and actual query patterns rather than choosing types solely by their storage size.

27. Official Documentation

For the complete technical specification, supported input formats, operators, precision limits and version-specific behavior, refer to the official PostgreSQL documentation:

• PostgreSQL 18 – Data Types
• PostgreSQL 18 – Numeric Types
• PostgreSQL 18 – Date/Time Types
• PostgreSQL 18 – JSON Types
• PostgreSQL 18 – Range Types
• PostgreSQL 18 – Pseudo-Types