当前位置：首页 > article >正文

postgresql 9.4.1 普通表，子表，父表的创建与测试

article 2025/1/29 22:03:51

– 创建父表
CREATE TABLE person (
id serial PRIMARY KEY,
first_name text,
last_name text,
birthdate timestamp
);

– 创建子表并继承父表的结构

CREATE TABLE person_202501 () INHERITS (person);
CREATE TABLE person_202412 () INHERITS (person);
CREATE TABLE person_202411 () INHERITS (person);
CREATE TABLE person_202410 () INHERITS (person);

–需要单独创建子表的索引
CREATE INDEX idx_child_col1 ON child_table (col1);

在PostgreSQL中，当创建一个继承子表时，父表的字段定义、默认值以及一些约束（如主键和唯一性约束）会被自动继承。然而，索引并不会被自动继承。这是PostgreSQL设计的一部分，有以下几个主要原因：

性能与灵活性考虑
独立优化：每个表的数据分布和访问模式可能不同，因此需要不同的索引来优化查询性能。如果强制继承父表的所有索引，可能会导致不必要的资源开销或不合适的索引选择。
灵活性：允许子表根据其特定的需求来创建适合自身的索引，提供了更大的灵活性。例如，某些子表可能需要额外的索引来支持特定的查询模式，而这些索引在父表中并不需要。
避免数据冗余
索引重复：如果索引被自动继承，那么在查询父表时，通过子表访问的数据也会被索引覆盖，这可能导致索引冗余，尤其是在多级继承结构中。
维护成本：索引的维护（插入、更新、删除操作）会增加额外的开销。如果所有子表都继承了父表的所有索引，那么对于大规模数据库来说，这种开销可能是显著的。
逻辑一致性
索引作用范围：索引通常是为了加速对特定表的查询而创建的。由于子表和父表的数据是分开存储的，父表上的索引只适用于父表的数据集。因此，将父表的索引直接应用于子表在逻辑上并不一致。

insert into person_202501 values (1001,‘fname’,‘lname’,‘2025-01-01 10:00:00’);
insert into person_202412 values (1001,‘fname’,‘lname’,‘2024-12-01 10:00:00’);
insert into person_202411 values (1001,‘fname’,‘lname’,‘2024-11-01 10:00:00’);
insert into person_202410 values (1001,‘fname’,‘lname’,‘2024-10-01 10:00:00’);

select * from only person;

select * from person_202501;

–清理子表过程
truncate table person_202410;
select count(*) from person_202410;
alter table person_202410 no inherit person;
drop table person_202410;

CREATE TABLE … (LIKE …) 语句可以用来创建一个新表，该新表具有与现有表相同的字段定义（包括字段类型、默认值等），但不会复制索引、约束或数据。

–创建一个与子表相同的表结构,不包含数据
CREATE TABLE new_person_202501 (LIKE person_202501 INCLUDING ALL);

–迁移数据：将原子表中的数据插入到新表中。
INSERT INTO new_person_202501 SELECT * FROM person_202501;

–删除旧的子表：解除原有的继承关系，并删除原来的子表。
ALTER TABLE person_202501 NO INHERIT person;
DROP TABLE person_202501;

–重命名新表：最后，如果你希望新表保留原来的名字，可以对其进行重命名。
ALTER TABLE new_child_table RENAME TO child_table;

–创建一个独立的表
CREATE TABLE person_dl (
id serial PRIMARY KEY,
first_name text,
last_name text,
birthdate timestamp
);

ALTER TABLE person_dl ADD PRIMARY KEY (id);

– 1. 添加主键约束
ALTER TABLE person_dl ADD PRIMARY KEY (order_id);

– 2. 创建普通索引
CREATE INDEX idx_date ON person_dl (birthdate);

– 3. 创建复合索引
CREATE INDEX idx_id_date ON person_dl (id, birthdate);

insert into person_dl values (1001,‘fname’,‘lname’,‘2024-10-01 10:00:00’);
insert into person_dl values (1002,‘fname’,‘lname’,‘2023-10-01 10:00:00’);
insert into person_dl values (1003,‘fname’,‘lname’,‘2022-10-01 10:00:00’);
insert into person_dl values (1004,‘fname’,‘lname’,‘2021-10-01 10:00:00’);

–复制表
CREATE TABLE person_dl_bak (LIKE person_dl INCLUDING ALL);

CREATE TABLE PERSON_DL_BAK2 AS SELECT * FROM PERSON_DL WHERE BIRTHDATE >=‘2023-10-01 10:00:00’;

注意：从另外一点可以看出，大小写没有不敏感

总结：

使用 CREATE TABLE … (LIKE …) 可以复制表结构，但不复制数据。
使用 CREATE TABLE … AS SELECT … 可以同时创建表并复制数据。
结合 CREATE TABLE … (LIKE … INCLUDING ALL) 和 INSERT INTO … SELECT … 可以完整地复制表结构（包括索引和约束）以及数据。

查看全文

http://www.kler.cn/a/522329.html