在一个语句中使用 PRIMARY KEY 创建 TABLE AS (PostgreSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11176383/
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
CREATE TABLE AS with PRIMARY KEY in one statement (PostgreSQL)
提问by TimY
Is there a way to set the PRIMARY KEY in a single "CREATE TABLE AS" statement?
有没有办法在单个“CREATE TABLE AS”语句中设置主键？
Example - I would like the following to be written in 1 statement rather than 2:
示例 - 我希望将以下内容写在 1 个语句中，而不是 2 个：
CREATE TABLE "new_table_name" AS SELECT a.uniquekey, a.some_value + b.some_value FROM "table_a" AS a, "table_b" AS b WHERE a.uniquekey=b.uniquekey;
ALTER TABLE "new_table_name" ADD PRIMARY KEY (uniquekey);
Is there a better way of doing this in general (assume there are more than 2 tables, e.g. 10)?
一般有没有更好的方法来做到这一点（假设有 2 个以上的表，例如 10 个）？
回答by peenut
According to the manual: create tableand create table asyou can either:
- create tablewith primary key first, and use select intolater
- create table asfirst, and use add primary keylater
- 创建表与主键第一，并使用选择进入后
- 创建表作为第一个，使用添加主键后
But not both create table aswith primary key- what you wanted.
但不能同时创建表作为与主键-你想要的东西。
回答by francs
If you want to create a new table with the same table structure of another table, you can do this in one statement (both creating a new table and setting the primary key) like this:
如果要创建与另一个表具有相同表结构的新表，可以在一个语句中执行此操作（创建新表并设置主键），如下所示：
create table mytable_clone (like mytable including defaults including constraints including indexes);
回答by a_horse_with_no_name
No, there is no shorter way to create the table and the primary key.
不，没有更短的方法来创建表和主键。
回答by user1188867
See the command below, it will create a new table with all the constraints and with no data. Worked in postgres 9.5
请参阅下面的命令，它将创建一个包含所有约束且没有数据的新表。在 postgres 9.5 中工作
CREATE TABLE IF NOT EXISTS <ClonedTableName>(like <OriginalTableName> including all)
回答by kaushikC
well in mysql ,both is possible in one command
在 mysql 中，两者都可以在一个命令中实现
the command is
命令是
create table new_tbl (PRIMARY KEY(`id`)) as select * from old_tbl;
where idis column with primary key of old_tbl
其中id是具有old_tbl主键列
done...
完毕...
回答by user5198207
You may do this way
你可以这样做
CREATE TABLE IOT (EMPID,ID,Name, CONSTRAINT PK PRIMARY KEY( ID,EMPID))
ORGANIZATION INDEX NOLOGGING COMPRESS 1 PARALLEL 4
AS SELECT 1 as empid,2 id,'XYZ' Name FROM dual;
