目录
核心数据类型深入探索
基础数值与文本类型
日期与时间类型
特殊与复合类型
JSON与HSTORE
表的创建、修改与管理
创建与修改表结构
索引与性能优化
约束与数据完整性
核心数据类型深入探索
PostgreSQL以其多样化的数据类型著称，不仅包括标准SQL数据类型，还有许多独特的高级类型，为数据建模提供了极高的灵活性。
基础数值与文本类型
-
整型与浮点型：
integer、bigint满足大多数整数需求，而real、double precision适合存储浮点数。例如，创建一个存储交易金额的表：CREATE TABLE transactions ( transaction_id serial PRIMARY KEY, amount numeric(10, 2) NOT NULL );字符类型：
varchar(n)用于变长字符串，text则无长度限制。使用char(n)固定长度字符串时需谨慎，因为未填满的空间会被空格填充。CREATE TABLE notes ( note_id serial PRIMARY KEY, content text );日期与时间类型
PostgreSQL提供多种时间相关类型，如
timestamp、date、time，且支持时区。例如，记录事件发生的时间戳：CREATE TABLE log_entries ( entry_id serial PRIMARY KEY, event_time timestamp with time zone DEFAULT now() );特殊与复合类型
-
数组类型：允许在单个列中存储同一类型元素的集合，如存储多电话号码。
CREATE TABLE contacts ( contact_id serial PRIMARY KEY, phone_numbers varchar(15)[] );枚举类型：用于存储预定义的值列表，增强数据一致性。
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending'); CREATE TABLE users ( user_id serial PRIMARY KEY, status status NOT NULL DEFAULT 'pending' );JSON与HSTORE
- JSON & JSONB：原生支持JSON数据存储，其中JSONB提供了更好的查询性能。
CREATE TABLE products (
product_id serial PRIMARY KEY,
details jsonb
);- HSTORE：轻量级键值对存储，适用于简单元数据。
CREATE TABLE books ( book_id serial PRIMARY KEY, meta_data hstore );表的创建、修改与管理
理解如何有效地创建、调整表结构，以及如何管理索引和约束，是数据库设计的关键。
创建与修改表结构
- 表创建：明确指定列的数据类型、约束条件，如外键。
CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id integer REFERENCES customers(customer_id),
order_date date NOT NULL
);- 修改表：使用
ALTER TABLE添加新列、修改列属性或删除列。ALTER TABLE orders ADD COLUMN total_amount numeric(10, 2);索引与性能优化
-
索引策略：基于查询模式选择合适类型的索引，如B-tree用于等值查询，GIN用于全文搜索。
CREATE INDEX idx_orders_customer_id ON orders USING btree (customer_id); -
覆盖索引：当索引包含查询所需的所有列时，可以避免访问表数据，提升性能。
-
常见约束：唯一约束(
UNIQUE)、非空约束(NOT NULL)、检查约束(CHECK)确保数据质量。
约束与数据完整性
常见约束：唯一约束(UNIQUE)、非空约束(NOT NULL)、检查约束(CHECK)确保数据质量。
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);外键约束：维护引用完整性，链接相关表。
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
