将两个表合并为一个具有相同列名的表 [英] Merging two tables into one with the same column names

问题描述

我使用此命令将2个表合并为一个表：

I use this command to merge 2 tables into one:

CREATE TABLE table1 AS 
SELECT name, sum(cnt)
FROM (SELECT * FROM table2 UNION ALL SELECT * FROM table3) X
GROUP BY name
ORDER BY 1;

表2 和 table3 是具有名为 name 和 cnt 的列的表，但结果表（ table1 ）具有列 name 和 sum 。

table2 and table3 are tables with columns named name and cnt, but the result table (table1) has the columns name and sum.

问题是如何更改命令，以便结果表具有列 name 和 cnt ？

The question is how to change the command so that the result table will have the columns name and cnt?

推荐答案

在没有显式名称的情况下，函数的输出会继承基本函数在Postgres中的名称。您可以在 SELECT 列表中使用列别名来解决此问题-例如 @hennes已提供。

In the absence of an explicit name, the output of a function inherits the basic function name in Postgres. You can use a column alias in the SELECT list to fix this - like @hennes already supplied.

如果您需要继承具有名称和类型（可能还有更多）的所有原始列，还可以使用单独的命令创建表：

If you need to inherit all original columns with name and type (and possibly more) you can also create the table with a separate command:

• 要仅复制具有名称和数据类型的列，请仍然使用 创建表AS ，但添加限制0 ：

CREATE TABLE table1 AS
TABLE table2 LIMIT 0;  -- "TABLE" is just shorthand for "SELECT * FROM" 

复制（< a href = http://www.postgresql.org/docs/current/interactive/sql-createtable.html rel = nofollow noreferrer>每个文档）：

所有列名，其数据类型及其非空约束：

all column names, their data types, and their not-null constraints:

CREATE TABLE table1 (LIKE table2);

...和（可选）也是默认值，约束，索引，注释和存储设置：

... and optionally also defaults, constraints, indexes, comments and storage settings:

CREATE TABLE table1 (LIKE table2 INCLUDING ALL);

...或者例如，默认值和约束条件：

... or, for instance, just defaults and constraints:

CREATE TABLE table1 (LIKE table2 INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

然后 INSERT ：

INSERT INTO table1 (name, cnt)
SELECT ...        -- column names are ignored

这篇关于将两个表合并为一个具有相同列名的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！