SQL Postgres - 从选择创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22953450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres - CREATE TABLE FROM SELECT
提问by user2923767
I have two tables, one contains a large list of IDs and Info regarding those ids.
我有两个表，一个包含有关这些 ID 的 ID 和信息的大列表。
I have a second table Graphwhich just has two columns, each column contains the aforementioned id numbers, multiple times. I want to trimthe size of my Infotable by selecting only those ids that appear in my graph and creating a new smaller Info table. Is there a simple way of doing this?
我有第二个表Graph，它只有两列，每列多次包含上述 ID 号。我想通过只选择出现在我的图表中的那些 id 并创建一个新的较小的信息表来trim调整我的Info表的大小。有没有一种简单的方法可以做到这一点？
CREATE TABLE FROM SELECT?
Thanks!
谢谢！
回答by a_horse_with_no_name
It's as easy as:
这很简单：
create table new_table
as
select t1.col1, t2.col2
from some_table t1
join t2 on t1.id = t2.some_id;
You can use any select statement for that. The column names of the new table are defined by the column aliases used in th query.
您可以为此使用任何选择语句。新表的列名由查询中使用的列别名定义。
More details in the manual: http://www.postgresql.org/docs/current/static/sql-createtableas.html
手册中的更多详细信息：http: //www.postgresql.org/docs/current/static/sql-createtableas.html
回答by Ilesh Patel
You can create TEMP table if you need those small table only for that session. you can use below query to do that.
如果您只需要该会话的那些小表，您可以创建 TEMP 表。您可以使用以下查询来做到这一点。
DROP TABLE IF EXISTS temp_table;
CREATE TEMP TABLE temp_table AS
SELECT
i.id as info_id, i.information as information
FROM
info i
INNER JOIN graph g ON i.id = g.id;
Now you can use this temp_table for your next table in the function.
现在您可以将此 temp_table 用于函数中的下一个表。
OR
you can also create table like below (if you not want to create it as TEMP):
您还可以创建如下表（如果您不想将其创建为 TEMP）：
CREATE TABLE temp_table AS
SELECT
i.id as info_id, i.information as information
FROM
info i
INNER JOIN graph g ON i.id = g.id;
