上一篇
pg数据库中创表语句怎么写？有哪些关键字和参数？
在pg数据库中,创表语句是数据操作的基础，通过CREATE TABLE命令可以定义表的结构，包括列名、数据类型、约束条件等，pg数据库作为开源的关系型数据库，其创表语句功能强大且灵活，支持多种数据类型和高级特性，能够满足不同业务场景的需求。
基本语法结构
pg数据库的创表语句基本语法如下：
table_name是表的名称，需遵循pg数据库的命名规范（以字母或下划线开头，可包含字母、数字和下划线）；column是列名，data_type指定列的数据类型，如INT、VARCHAR、TIMESTAMP等；constraint是列级约束，如NOT NULL、UNIQUE、PRIMARY KEY等；table_constraints是表级约束，如FOREIGN KEY、CHECK等。
常用数据类型
pg数据库支持丰富的数据类型,以下是常用的几种：
- 数值类型：包括SMALLINT（2字节整数）、INT或INTEGER（4字节整数）、BIGINT（8字节整数）、DECIMAL（精确小数）、REAL或FLOAT4（单精度浮点数）、DOUBLE PRECISION或FLOAT8（双精度浮点数）等。
- 字符类型：CHAR(n)（固定长度字符串）、VARCHAR(n)（可变长度字符串）、TEXT（无长度限制的文本字符串）。
- 日期时间类型：DATE（日期，仅存储年月日）、TIME（时间，仅存储时分秒）、TIMESTAMP（日期时间，存储年月日时分秒）、INTERVAL（时间间隔）。
- 布尔类型：BOOLEAN，取值为TRUE、FALSE或NULL。
- 二进制类型：BYTEA，用于存储二进制数据。
- 数组类型：如INT[]（整数数组）、TEXT[]（文本数组），支持存储数组数据。
- JSON类型：JSON（存储JSON数据）、JSONB（二进制存储的JSON数据，支持索引和高效查询）。
约束条件
约束条件用于保证数据的完整性和一致性,主要分为列级约束和表级约束：
- NOT NULL：确保列不能存储NULL值，例如CREATE TABLE users (id INT NOT NULL, name VARCHAR(50));。
- UNIQUE：确保列中的值唯一，可以添加多个列的联合唯一约束，例如CREATE TABLE products (id INT UNIQUE, code VARCHAR(20) UNIQUE);。
- PRIMARY KEY：唯一标识表中的每一行，相当于NOT NULL和UNIQUE的组合，一个表只能有一个主键，例如CREATE TABLE orders (id INT PRIMARY KEY, order_date TIMESTAMP);。
- FOREIGN KEY：实现表之间的引用完整性，引用其他表的主键，例如CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));。
- CHECK：确保列中的值满足特定条件，例如CREATE TABLE employees (id INT, age INT CHECK (age > 18));。
- DEFAULT：为列指定默认值，当插入数据未提供该列值时自动使用默认值，例如CREATE TABLE products (id INT, status VARCHAR(10) DEFAULT 'active');。
高级特性
pg数据库的创表语句还支持多种高级特性,
- 继承表：通过INHERITS关键字实现表继承，子表自动继承父表的列和约束，例如CREATE TABLE child_table (column1 INT) INHERITS (parent_table);。
- 分区表：通过PARTITION BY子句实现表分区，提高大数据量下的查询性能，支持范围分区、列表分区、哈希分区等，例如CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date);。
- 临时表：使用TEMP或TEMPORARY关键字创建临时表，临时表会话结束后自动删除，例如CREATE TEMP TABLE temp_data (id INT, value VARCHAR(50));。
- OID列：通过WITH OIDS选项添加对象标识符列（oid），但pg 12版本后默认不推荐使用，建议使用pg_class表的relfilenode替代。
- 存储参数：通过WITH (storage_parameter)设置表的存储参数，如fillfactor（填充因子）、autovacuum_enabled（是否启用自动清理）等，例如CREATE TABLE logs (id INT, message TEXT) WITH (fillfactor=70);。
示例
以下是一个综合示例,创建一个包含多种数据类型和约束的表：
CREATE TABLE customers ( id SERIAL PRIMARY KEY, 自增主键 name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, age INT CHECK (age >= 18), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE, address JSONB, orders_count INT DEFAULT 0 );
SERIAL是自增类型，相当于INT和SEQUENCE的组合，自动创建序列并关联到列。
表格归纳常用数据类型和约束
| 类型/约束 | 说明 | 示例 |
|---|---|---|
| SERIAL | 自增整数 | id SERIAL PRIMARY KEY |
| VARCHAR(n) | 可变长度字符串 | name VARCHAR(50) |
| TEXT | 无长度限制文本 | description TEXT |
| TIMESTAMP | 日期时间 | created_at TIMESTAMP |
| BOOLEAN | 布尔值 | is_active BOOLEAN |
| JSONB | 二进制JSON | address JSONB |
| NOT NULL | 非空约束 | name VARCHAR(100) NOT NULL |
| UNIQUE | 唯一约束 | email VARCHAR(255) UNIQUE |
| PRIMARY KEY | 主键 | id INT PRIMARY KEY |
| FOREIGN KEY | 外键 | user_id INT REFERENCES users(id) |
| CHECK | 条件约束 | age INT CHECK (age > 0) |
| DEFAULT | 默认值 | status VARCHAR(10) DEFAULT ‘active’ |
相关问答FAQs
Q1：如何在pg数据库中修改已创建表的结构？
A1：使用ALTER TABLE语句可以修改表的结构，例如添加列（ALTER TABLE table_name ADD COLUMN column_name data_type;）、修改列类型（ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;）、删除列（ALTER TABLE table_name DROP COLUMN column_name;）、添加约束（ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);）等，需要注意的是，修改表结构可能会影响现有数据，操作前建议备份数据。
Q2：pg数据库中的SERIAL和BIGSERIAL有什么区别？
A2：SERIAL和BIGSERIAL都是pg数据库提供的自增类型，用于生成唯一标识符，主要区别在于数据类型和范围：SERIAL对应INT（4字节），取值范围为2^31到2^311，实际自增范围为1到2^311；BIGSERIAL对应BIGINT（8字节），取值范围为2^63到2^631，实际自增范围为1到2^631，当需要生成的自增值超过INT范围时，应使用BIGSERIAL，例如大数据量表或需要长期自增的场景。
