all types postgresql

gistfile1.txt

-- 1. Create a custom Enum type (required before table creation)
CREATE TYPE mood_enum AS ENUM ('sad', 'okay', 'happy');

-- 2. Create the table with all standard PostgreSQL types
CREATE TABLE all_postgres_types (
    -- Numeric Types
    col_smallint       SMALLINT,
    col_integer        INTEGER,
    col_bigint         BIGINT,
    col_decimal        DECIMAL,
    col_numeric        NUMERIC(10,2),
    col_real           REAL,
    col_double         DOUBLE PRECISION,
    col_smallserial    SMALLSERIAL,
    col_serial         SERIAL,
    col_bigserial      BIGSERIAL,
    col_money          MONEY,

    -- Character Types
    col_char           CHAR(10),
    col_varchar        VARCHAR(255),
    col_text           TEXT,

    -- Boolean
    col_boolean        BOOLEAN,

    -- Date/Time Types
    col_date           DATE,
    col_time           TIME,
    col_time_tz        TIME WITH TIME ZONE,
    col_timestamp      TIMESTAMP,
    col_timestamp_tz   TIMESTAMP WITH TIME ZONE,
    col_interval       INTERVAL,

    -- Binary / Bit String
    col_bytea          BYTEA,
    col_bit            BIT(8),
    col_varbit         BIT VARYING(8),

    -- Network Address
    col_macaddr        MACADDR,
    col_macaddr8       MACADDR8,
    col_inet           INET,
    col_cidr           CIDR,

    -- Text Search
    col_tsvector       TSVECTOR,
    col_tsquery        TSQUERY,

    -- UUID
    col_uuid           UUID,

    -- XML
    col_xml            XML,

    -- JSON
    col_json           JSON,
    col_jsonb          JSONB,

    -- Arrays
    col_int_array      INTEGER[],

    -- Ranges
    col_int4range      INT4RANGE,
    col_tsrange        TSRANGE,
    col_daterange      DATERANGE,

    -- Composite / Special
    col_mood           mood_enum,
    col_oid            OID,
    col_point          POINT,
    col_line           LINE,
    col_lseg           LSEG,
    col_box            BOX,
    col_path           PATH,
    col_polygon        POLYGON,
    col_circle         CIRCLE,

    -- Large Objects (typically stored separately, but the type exists)
    -- col_pg_lsn      PG_LSN, -- Commented out as usually reserved for WAL
    col_txid_snapshot  TXID_SNAPSHOT
);


-- A. BTREE (The Default)
-- Use for: Equality checks, range queries, sorting on standard data types.
CREATE INDEX idx_btree_int ON all_postgres_types USING BTREE (col_integer);
CREATE INDEX idx_btree_text ON all_postgres_types USING BTREE (col_text);
CREATE INDEX idx_btree_date ON all_postgres_types USING BTREE (col_timestamp_tz);

-- B. HASH (Equality only)
-- Use for: Simple equality comparisons (often faster than B-Tree for large datasets where sorting isn't needed).
-- Note: Hash indexes are not WAL-logged (before PG 10) and generally not transaction-safe in crash recovery scenarios in older versions, but are fine in modern PG for simple lookups.
CREATE INDEX idx_hash_uuid ON all_postgres_types USING HASH (col_uuid);

-- C. GIN (Generalized Inverted Index)
-- Use for: Arrays, JSONB, Hstore, Full Text Search (TsVector). 
-- Ideal when you want to find rows containing a specific element *inside* the column.
CREATE INDEX idx_gin_jsonb ON all_postgres_types USING GIN (col_jsonb);
CREATE INDEX idx_gin_array ON all_postgres_types USING GIN (col_int_array);
CREATE INDEX idx_gin_tsvector ON all_postgres_types USING GIN (col_tsvector);

-- D. GiST (Generalized Search Tree)
-- Use for: Geometric data, Full Text Search, Ranges.
-- Ideal for "nearest neighbor" searches or overlapping ranges.
CREATE INDEX idx_gist_point ON all_postgres_types USING GIST (col_point);
CREATE INDEX idx_gist_box ON all_postgres_types USING GIST (col_box);
CREATE INDEX idx_gist_range ON all_postgres_types USING GIST (col_int4range);
-- GiST for TsVector allows "contains" or "proximity" operators
CREATE INDEX idx_gist_tsvector ON all_postgres_types USING GIST (col_tsvector);

-- E. SP-GiST (Space-Partitioned GiST)
-- Use for: Data that can be partitioned (like Quad-trees for points or KD-trees).
-- Often faster than GiST for non-overlapping data like phone numbers or radial point searches.
CREATE INDEX idx_spgist_point ON all_postgres_types USING SPGIST (col_point);
CREATE INDEX idx_spgist_inet ON all_postgres_types USING SPGIST (col_inet);

-- F. BRIN (Block Range Indexes)
-- Use for: Massive tables where data is physically sorted (e.g., time-series).
-- Extremely small size, scans the whole table header blocks.
CREATE INDEX idx_brin_date ON all_postgres_types USING BRIN (col_date);


INSERT INTO all_postgres_types (
    col_smallint, col_integer, col_bigint, col_numeric, col_real, col_money,
    col_varchar, col_text, col_boolean, col_date, col_timestamp_tz, col_interval,
    col_bytea, col_bit, col_inet, col_tsvector, col_uuid, col_jsonb,
    col_int_array, col_int4range, col_mood, col_point, col_box, col_polygon
) VALUES (
    100, 50000, 9000000000, 12345.67, 3.14159, '$12,000.00',
    'Fixed Width', 'Arbitrary length text string', TRUE, 
    '2023-10-27', '2023-10-27 14:30:00+00', '1 year 2 months',
    E'\\xDEADBEEF', B'10101010', '192.168.1.1/24',
    to_tsvector('english', 'The quick brown fox jumps over the lazy dog'),
    gen_random_uuid(), 
    '{"status": "active", "tags": ["postgres", "database"], "priority": 1}'::jsonb,
    ARRAY[1, 2, 3, 4, 5],
    '[1,10)', -- Integer range 1 to 10 (inclusive-exclusive)
    'happy',
    point(10.5, 20.5),
    box(point(0,0), point(10,10)),
    polygon(circle(point(5,5), 5))
);