PostgreSQL 使用其他表作为模板创建新表

最新推荐文章于 2023-02-27 15:51:26 发布

面汤放盐

最新推荐文章于 2023-02-27 15:51:26 发布

阅读量1.2k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： PostgreSQL

本文链接：https://blog.csdn.net/qq_31156277/article/details/98516962

PostgreSQL 专栏收录该内容

56 篇文章

订阅专栏

本文介绍如何在PostgreSQL中使用其他表作为模板创建新表，包括如何复制表结构、索引、主键等所有属性，适用于继承、分表和复制等场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用其他表作为模板创建新表

创建表tbl_big 并利用该表为模板，快速创建表 ( like tbl_big including all )

[root@uzong ~]# su postgres
bash-4.2$ psql technology
could not change directory to "/root"
psql (9.2.24, server 10.8)
WARNING: psql version 9.2, server version 10.0.
         Some psql features might not work.
Type "help" for help.

technology=# create table tbl_big (
technology(# id int primary key, 
technology(# info text, 
technology(# crt_time timestamp
technology(# );
CREATE TABLE
technology=# create index idx_tbl_big on tbl_big (crt_time); 
CREATE INDEX
technology=# \d tbl_big;
               Table "public.tbl_big"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | not null
 info     | text                        | 
 crt_time | timestamp without time zone | 
Indexes:
    "tbl_big_pkey" PRIMARY KEY, btree (id)
    "idx_tbl_big" btree (crt_time)

technology=# create table tbl ( like tbl_big including all );
CREATE TABLE
technology=# \d tbl;
                 Table "public.tbl"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | not null
 info     | text                        | 
 crt_time | timestamp without time zone | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)
    "tbl_crt_time_idx" btree (crt_time)

通过( like tbl_big including all ) 可以快速复制一个表解构（包括索引，主键等各种信息）

更多的参数：

including defults

including constraints

including indexes

including storage

including comments

including all  -- 全部

总结

关于使用其他表作为模板创建新的表适用场景，适用于继承、分表，复制等情况