PostgreSQL SELECT INTO和INSERT INTO SELECT 两种表复制语句

SELECT INTO和INSERT INTO SELECT两种表复制语句都可以用来复制表与表之间的数据,但是它们之间也有区别。

建表语句:

bas_custom_rel表

CREATE TABLE "public"."bas_custom_rel" (
"uuid" int8 NOT NULL,
"kunnrkh" varchar(100) COLLATE "default",
"zfdel" varchar(1) COLLATE "default",
"hkunnrkh" varchar(100) COLLATE "default",
"create_time" timestamp(6) NOT NULL,
"modify_time" timestamp(6),
"sync_status" varchar(1) COLLATE "default",
"sync_time" timestamp(6),
"ret_code" varchar(100) COLLATE "default",
"ret_message" varchar(5000) COLLATE "default",
"z_date" timestamp(6),
CONSTRAINT "pk_bas_custom_rel" PRIMARY KEY ("uuid")
)

cust_rel表:

CREATE TABLE "public"."cust_rel" (
"son" varchar(255) COLLATE "default",
"pp" varchar(255) COLLATE "default"
)

 

1. INSERT INTO FROM语句

 语句形式为:Insert into Table2(field1,field2,…) select value1,value2,… from Table1

 要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。示例如下:
 

INSERT INTO bas_custom_rel (
 uuid,
 kunnrkh,
 zfdel,
 hkunnrkh,
 create_time,
 modify_time,
 sync_status,
 sync_time,
 ret_code,
 ret_message,
 z_date
) SELECT
 nextval('cust_rel_seq') AS uuid,
 son,
 '' AS zfdel,
 coalesce(pp,'-1') AS pp,
 now() as create_time,
 NULL AS modify_time,
 0 AS sync_status,
 NULL AS sync_time,
 0 as ret_code,
 '' AS ret_message,
 now()
FROM
 cust_rel;

2.SELECT INTO FROM语句

 语句形式为:SELECT vale1, value2 into Table2 from Table1

 要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。示例如下:
 

postgres=# drop table tb101; DROP TABLE
postgres=#
postgres=# select * into tb101 from tb100 where id<5; SELECT 4
postgres=#
postgres=# select * from tb101; id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa (4 rows)

3、复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页