瀚高/PG复制表结构的sql语法

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：9.0.4

文档用途

本文主要用于介绍几种复制表结构的sql语法及区别

详细信息

一、示例语法及区别总结

二、详细测试用例

1.创建源表 t2

DROPTABLEIFEXISTSt2;CREATETABLEt2(idSERIALPRIMARYKEY,nameTEXTNOTNULL,ageINTCHECK(age>0),created_atTIMESTAMPDEFAULTnow(),codeTEXTUNIQUE,remarkTEXT,statusINTGENERATED ALWAYSASIDENTITY);COMMENTONTABLEt2IS'源表 t2';COMMENTONCOLUMNt2.nameIS'姓名';COMMENTONCOLUMNt2.ageIS'年龄';

2.创建索引（非约束索引）

CREATEINDEXidx_t2_nameONt2(name);

3.插入测试数据

INSERTINTOt2(name,age,code,remark)VALUES('Alice',20,'A001','test1'),('Bob',30,'B001','test2');

4.分别使用三种sql语法进行测试

postgres=# CREATE TABLE t1 AS TABLE t2; --查询表结构可看到无索引、约束、注释等SELECT2postgres=# \d+ t1Table"public.t1"Column|Type|Collation|Nullable|Default|Storage|Compression|Stats target|Description------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------id|integer||||plain|||name|text||||extended|||age|integer||||plain|||created_at|timestampwithouttimezone||||plain|||code|text||||extended|||remark|text||||extended|||status|integer||||plain|||Access method: heap postgres=# drop table t1;DROPTABLEpostgres=# create table t1 as select * from t2; --查询表结构可看到无索引、约束、注释等SELECT2postgres=# \d+ t1;Table"public.t1"Column|Type|Collation|Nullable|Default|Storage|Compression|Stats target|Description------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------id|integer||||plain|||name|text||||extended|||age|integer||||plain|||created_at|timestampwithouttimezone||||plain|||code|text||||extended|||remark|text||||extended|||status|integer||||plain|||Access method: heap postgres=# drop table t1;DROPTABLEpostgres=# CREATE TABLE t1 (LIKE t2 INCLUDING ALL); --查询表结构可看到索引、约束、注释等CREATETABLEpostgres=# \d+ t1;Table"public.t1"Column|Type|Collation|Nullable|Default|Storage|Compression|Stats target|Description------------+-----------------------------+-----------+----------+--------------------------------+----------+-------------+--------------+-------------id|integer||notnull|nextval('t2_id_seq'::regclass)|plain|||name|text||notnull||extended|||姓名 age|integer||||plain|||年龄 created_at|timestampwithouttimezone|||now()|plain|||code|text||||extended|||remark|text||||extended|||status|integer||notnull|generated alwaysasidentity|plain|||Indexes:"t1_pkey"PRIMARYKEY,btree(id)"t1_code_key"UNIQUECONSTRAINT,btree(code)"t1_name_idx"btree(name)Checkconstraints:"t2_age_check"CHECK(age>0)Access method: heap

相关文档