postgres创建和数据表相同结构的表的方法

lk_db

于 2017-09-20 11:57:25 发布

阅读量3.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Highgo DB PostgreSQL

本文链接：https://blog.youkuaiyun.com/lk_db/article/details/78039234

Highgo DB 同时被 2 个专栏收录

171 篇文章

订阅专栏

PostgreSQL

162 篇文章

订阅专栏

在Postgres中，可以使用不同的方法复制表结构和/或数据。`CREATE TABLE AS SELECT * FROM` 语句能同时复制结构和数据，而`CREATE TABLE LIKE` 只复制结构。若想只复制结构不带数据，可以使用 `WITH NO DATA` 子句。尝试使用 `INCLUDING ALL` 选项时会出现语法错误，表明Postgres不支持这种方式。

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

highgo=# select * from test;
id
------
1
1
1
1
1
100
200
300
400
400
1000
2000
3000
3000
3000
1
(16 rows)

highgo=# create table testa as select * from test; -此方式即有表结构又包含数据
SELECT 16
highgo=# create table testb (like test); --此方式仅创建表结构，没有数据
CREATE TABLE
highgo=#
highgo=# select * from testa;
id
------
1
1
1
1
1
100
200
300
400
400
1000
2000
3000
3000
3000
1
(16 rows)

highgo=# select * from testb;
id
----
(0 rows)

highgo=# create table testc as select * from test with no data; ---此方式效果和like方式相同
CREATE TABLE AS
highgo=#
highgo=# select * from testc;
id
----
(0 rows)

使用like方式时，有如下选项:
highgo=# create table testc as select * from test with no data including all;
错误: 语法错误在 "including" 或附近的
LINE 1: ...te table testc as select * from test with no data including ...
^
highgo=#
highgo=# create table testd (like test) including all;
错误: 语法错误在 "including" 或附近的
LINE 1: create table testd (like test) including all;
^
highgo=# create table testd (like test including all);
CREATE TABLE
including default
including constraints
including indexes
including storage
including comments
including all---把所有属性都复制过去