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 旧表