PostgreSQL: PostgreSQL数据类型完整指南

-- Correct type choices for an e-commerce database
CREATE TABLE smart_products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- future-proof ID
    name VARCHAR(200) NOT NULL,                          -- bounded string
    description TEXT,                                    -- unbounded text
    price DECIMAL(10, 2) NOT NULL,                       -- exact money
    weight_kg REAL,                                      -- approximate is OK
    is_available BOOLEAN DEFAULT true,                   -- yes/no flag
    created_at TIMESTAMPTZ DEFAULT NOW(),                -- timezone-aware
    source_ip INET,                                      -- IP address type
    attributes JSONB DEFAULT '{}'::jsonb                 -- flexible schema
);

-- SMALLINT for small-range values
CREATE TABLE ratings (
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    score SMALLINT CHECK (score BETWEEN 1 AND 5),  -- 1-5 is well within SMALLINT range
    PRIMARY KEY (user_id, product_id)
);

-- INTEGER for most IDs
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,  -- SERIAL = INTEGER + auto-increment sequence
    name VARCHAR(100) NOT NULL
);

-- BIGINT for high-growth tables
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,  -- BIGSERIAL = BIGINT + auto-increment
    action VARCHAR(50),
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE

-- SQL-standard identity column (PostgreSQL 10+)
CREATE TABLE orders_v2 (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(12, 2) DEFAULT 0
);

-- Insert without specifying id (auto-generated)
INSERT INTO orders_v2 (user_id, total) VALUES (1, 99.99);

-- Attempt to manually insert id (will FAIL with GENERATED ALWAYS)
-- INSERT INTO orders_v2 (id, user_id, total) VALUES (100, 1, 50.00);

-- Use GENERATED BY DEFAULT if you need manual override sometimes
CREATE TABLE logs (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    message TEXT
);

INSERT 0 1

-- Float precision trap: 0.1 + 0.2 != 0.3
SELECT 0.1::REAL + 0.2::REAL = 0.3::REAL AS float_equal;
-- Output: f (false!)

-- DECIMAL has no precision loss
SELECT 0.1::DECIMAL + 0.2::DECIMAL = 0.3::DECIMAL AS decimal_equal;
-- Output: t (true!)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- DECIMAL(precision, scale)
-- precision = total digits, scale = digits after decimal point
-- DECIMAL(10,2) = up to 99,999,999.99

CREATE TABLE products_precise (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10, 2) NOT NULL,       -- max 99,999,999.99
    tax_rate DECIMAL(5, 4) DEFAULT 0.0875, -- 0.0875 = 8.75%
    discount NUMERIC(5, 2) DEFAULT 0.00   -- max 999.99%
);

-- Money calculations are exact
SELECT name,
       price,
       price * tax_rate AS tax_amount,
       price + (price * tax_rate) AS price_with_tax
FROM products_precise;

 count 
-------
     5
(1 row)

-- CHAR: fixed-length (padded with spaces)
SELECT LENGTH('abc'::CHAR(5));
-- Output: 5 (padded to 5 chars)

-- VARCHAR: variable-length with limit
SELECT LENGTH('abc'::VARCHAR(5));
-- Output: 3 (stored as-is, max 5)

-- TEXT: variable-length, no limit
SELECT LENGTH('abc'::TEXT);
-- Output: 3 (no limit)

-- Performance test: VARCHAR vs TEXT are identical in PG
-- (Unlike MySQL where VARCHAR is faster than TEXT)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Set timezone to UTC
SET timezone = 'UTC';

-- Insert the same moment in different types
INSERT INTO test_times (ts_no_tz, ts_with_tz) VALUES
    ('2026-07-13 10:00:00', '2026-07-13 10:00:00+00');

-- Change to Tokyo timezone
SET timezone = 'Asia/Tokyo';

-- TIMESTAMP (no timezone) shows the same literal
SELECT ts_no_tz FROM test_times;
-- Output: 2026-07-13 10:00:00 (unchanged, could be any timezone!)

-- TIMESTAMPTZ converts to local timezone
SELECT ts_with_tz FROM test_times;
-- Output: 2026-07-13 19:00:00+09 (10:00 UTC = 19:00 Tokyo)

INSERT 0 1

-- Current date and time
SELECT NOW();                    -- 2026-07-13 10:30:00.123456+00
SELECT CURRENT_DATE;             -- 2026-07-13
SELECT CURRENT_TIMESTAMP;        -- same as NOW()

-- Date arithmetic with INTERVAL
SELECT NOW() + INTERVAL '7 days';     -- one week from now
SELECT NOW() - INTERVAL '3 months';   -- 3 months ago

-- Age calculation
SELECT AGE(TIMESTAMP '1990-05-15');   -- 36 years 1 month 28 days

-- Extract parts
SELECT EXTRACT(YEAR FROM NOW());      -- 2026
SELECT EXTRACT(MONTH FROM NOW());     -- 7
SELECT EXTRACT(DOW FROM NOW());       -- 1 (Monday, 0=Sunday)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Enable uuid-ossp extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Generate different UUID versions
SELECT uuid_generate_v4();  -- random UUID (most common)
SELECT uuid_generate_v1();  -- time-based UUID

-- INET: single IP or IP range
CREATE TABLE access_log (
    id BIGSERIAL PRIMARY KEY,
    source_ip INET NOT NULL,
    access_time TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO access_log (source_ip) VALUES
    ('192.168.1.100'),
    ('10.0.0.5'),
    ('2001:db8::1');

-- Query: find all IPs in a subnet (impossible with VARCHAR!)
SELECT source_ip FROM access_log
WHERE source_ip << '192.168.1.0/24'::INET;
-- << means "is contained within"

-- CIDR: network range
SELECT '192.168.1.0/24'::CIDR;

INSERT 0 1

-- Boolean accepts multiple representations
SELECT true, 't', 'true', 'yes', 'on', '1';   -- all = TRUE
SELECT false, 'f', 'false', 'no', 'off', '0';  -- all = FALSE

-- Boolean in WHERE clause
SELECT name FROM products WHERE is_available IS TRUE;
SELECT name FROM products WHERE is_available IS NOT FALSE;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Create an enum type (once, reusable across tables)
CREATE TYPE order_status AS ENUM (
    'pending', 'paid', 'shipped', 'delivered', 'cancelled'
);

-- Use in table definition
CREATE TABLE orders_enum (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- Enum values are validated automatically
INSERT INTO orders_enum (status) VALUES ('pending');   -- OK
-- INSERT INTO orders_enum (status) VALUES ('unknown');  -- ERROR!

INSERT 0 1

-- ============================================
-- Complete example: table with all major types
-- A sensor data collection system
-- ============================================

-- Create enum type
CREATE TYPE sensor_status AS ENUM ('active', 'inactive', 'maintenance');

-- Create table with diverse data types
CREATE TABLE sensors (
    -- Identity
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4() UNIQUE,

    -- Text
    name VARCHAR(100) NOT NULL,
    description TEXT,
    location_code CHAR(10),

    -- Numeric
    latitude DECIMAL(9, 6) CHECK (latitude BETWEEN -90 AND 90),
    longitude DECIMAL(9, 6) CHECK (longitude BETWEEN -180 AND 180),
    altitude REAL,

    -- Network
    ip_address INET,
    subnet CIDR,

    -- Status
    status sensor_status DEFAULT 'active',
    is_online BOOLEAN DEFAULT false,

    -- Time
    installed_at DATE,
    last_reading_at TIMESTAMPTZ,
    reading_interval INTERVAL DEFAULT INTERVAL '5 minutes',

    -- Flexible data
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample data
INSERT INTO sensors (name, latitude, longitude, ip_address, status, installed_at, metadata)
VALUES (
    'Temperature Sensor A1',
    35.6762, 139.6503,
    '192.168.1.50',
    'active',
    '2025-01-15',
    '{"model": "TX-200", "unit": "celsius", "range_min": -40, "range_max": 85}'::jsonb
);

-- Query with type-specific operators
SELECT name, ip_address, metadata->>'model' AS model
FROM sensors
WHERE ip_address << '192.168.1.0/24'::INET
  AND status = 'active';