【PostgreSQL】数据基石：PostgreSQL的数据类型与表结构设计

目录

 核心数据类型深入探索

基础数值与文本类型

日期与时间类型

特殊与复合类型

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);