PostgreSQL offers a rich set of data types to handle various kinds of data efficiently. Understanding these data types is crucial for designing robust database schemas.

Integer Types

Integer Types:

PostgreSQL supports several integer types: SMALLINT, INTEGER, and BIGINT. These are used to store whole numbers of varying sizes.


CREATE TABLE numbers (
  small_number SMALLINT,
  normal_number INTEGER,
  large_number BIGINT
);

Character Types

Character Types:

Character types include CHAR, VARCHAR, and TEXT, which are used for storing string data of varying lengths.


CREATE TABLE texts (
  fixed_char CHAR(10),
  variable_char VARCHAR(50),
  long_text TEXT
);

Numeric Types

Numeric Types:

PostgreSQL provides numeric types like NUMERIC and DECIMAL for precision arithmetic operations.


CREATE TABLE prices (
  price NUMERIC(10, 2),
  discount DECIMAL(5, 2)
);

Date/Time Types

Date/Time Types:

These types include DATE, TIME, TIMESTAMP, and INTERVAL, which are essential for handling time-based data.


CREATE TABLE events (
  event_date DATE,
  event_time TIME,
  event_timestamp TIMESTAMP
);

Boolean Type

Boolean Type:

The BOOLEAN type is used to store true/false values.


CREATE TABLE flags (
  is_active BOOLEAN
);

Array Type

Array Type:

PostgreSQL supports arrays, allowing columns to store multiple values of the same data type.


CREATE TABLE students (
  name TEXT,
  grades INTEGER[]
);

JSON Type

JSON Type:

The JSON and JSONB types allow you to store JSON data, with JSONB being more efficient for querying.


CREATE TABLE documents (
  data JSONB
);

UUID Type

UUID Type:

The UUID type is used for storing universally unique identifiers, which are useful in distributed systems.


CREATE TABLE items (
  id UUID DEFAULT gen_random_uuid()
);

Geometric Types

Geometric Types:

Geometric types such as POINT, LINE, and POLYGON are used for geometric data storage.


CREATE TABLE shapes (
  location POINT,
  boundary POLYGON
);

Network Address Types

Network Address Types:

These types include CIDR, INET, and MACADDR, which are used for storing network addresses.


CREATE TABLE networks (
  ip_address INET,
  mac_address MACADDR
);