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

2019独角兽企业重金招聘Python工程师标准>>>

一、INSERT INTO FROM语句
    语句形式为：INSERT INTO table2(field1,field2,…) SELECT value1,value2,… from table1 要求目标表table2必须存在。

    1、创建临时表：

        create temporary table tmp_idfa_fm (id bigserial primary key,idfa text, num int);

    2、插入数据：

        insert into tmp_idfa_fm (idfa, num) select idfa, count(idfa) as ct from nlogs where idfa!='' and sendtime>='2015-09-01 00:00:00' and sendtime<'2015-12-01 00:00:00' group by idfa order by ct desc ;

 

二、SELECT INTO FROM语句

    语句形式为：SELECT vale1, value2 into table2 FROM table1

    要求目标表table2不存在，因为在插入时会自动创建表table2，并将table1中指定字段数据复制到table2中。

    查询语句：

        select idfa, recvtime into tmp_tb1 from nlogs where recvtime>='2015-09-01 00:00:00' and recvtime<'2015-09-03 00:00:00';

 

 

转载于:https://my.oschina.net/u/3625745/blog/3000257