参考博客:https://blog.csdn.net/wlwlwlwl015/article/details/52493197
列属性和数据被拷贝,但不包含约束(如非空)、注释和序列
CREATE TABLE dest as SELECT * from src;
只复制表结构(默认复制非空约束),没有数据
CREATE TABLE dest (like src);
如果希望索引、主键约束和唯一约束被复制的话,那么需要在后面加上参数including indexes(大小写无关)
CREATE TABLE dest (like src including indexes);
其他参数如下:
更改分布键
ALTER TABLE dest set distributed by(columname);
PS:若有主键,先删除主键关联,再更改
查询主键名,一般主键名都是表名 + _pkey 如表dest 主键名就是 dest_pkey
SELECT a.conname FROM pg_constraint as a, pg_class as b
WHERE a.conrelid = b.oid AND a.contype = 'p' AND b.relname = 'dest';
删除主键
ALTER TABLE dest DROP CONSTRAINT dest_pkey;