PostgreSQL advanced type syntax: (1) UUID: gen_random_uuid() generates UUID, cast with ::uuid. (2) INET: Cast with ::inet, operators << (contained by), >> (contains). (3) Range: [lower, upper) for inclusive/exclusive, operators @> (contains), && (overlaps). (4) ENUM: CREATE TYPE name AS ENUM (values), type-safe. (5) POINT: POINT(x, y), operator <-> (distance). (6) Composite: CREATE TYPE name AS (fields), access with (column).field.

UUID: gen_random_uuid() generates UUID, DEFAULT gen_random_uuid() for auto-generation, 16 bytes storage

INET: Cast with ::inet, operators << (contained by network), >> (contains network), 16 bytes for IPv4/IPv6

Range types: [lower, upper) syntax, [inclusive, exclusive), operators @> (contains), && (overlaps), -|- (adjacent)

ENUM: CREATE TYPE name AS ENUM (values), type-safe, 4 bytes storage, ALTER TYPE name ADD VALUE to add values

POINT: POINT(x, y) syntax, operator <-> (distance), use with PostGIS for advanced GIS

Composite: CREATE TYPE name AS (fields), access with (column).field, nested structures

Exclusion constraints: EXCLUDE USING GIST (column WITH operator), prevent overlaps/conflicts at database level

Rich type system: UUID (16 bytes), INET (network operators), Range types (exclusion constraints), ENUM (type-safe), Geometric types (PostGIS), Composite types

Limited types: No UUID (use BINARY(16) or VARCHAR(36)), No INET (use VARCHAR), No Range types, No ENUM (deprecated), No Geometric types (basic only), No Composite types

Different types: UNIQUEIDENTIFIER (UUID equivalent), No INET (use VARCHAR), No Range types, No ENUM (use CHECK constraint), Geometric types (different syntax), No Composite types

Enterprise types: RAW(16) for UUID, No INET (use VARCHAR), No Range types, No ENUM (use CHECK constraint), SDO_GEOMETRY for GIS, Object types (similar to Composite)

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY

UNIQUE

Prevents duplicate values in a column or column combination.

email VARCHAR(255) UNIQUE

CHECK

Validates a row-level rule whenever data is inserted or updated.

CHECK (salary >= 0)

ROWS / RANGE