PostgreSQL：你能在 CREATE TABLE 定义中创建索引吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6239657/
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
PostgreSQL: Can you create an index in the CREATE TABLE definition?
提问by Xeoncross
I want to add indexes to some of the columns in a table on creation. Is there are way to add them to the CREATE TABLE definition or do I have to add them afterward with another query?
我想在创建时为表中的某些列添加索引。有没有办法将它们添加到 CREATE TABLE 定义中，还是我必须在之后使用另一个查询添加它们？
CREATE INDEX reply_user_id ON reply USING btree (user_id);
回答by rid
There doesn't seem to be any way of specifying an index in the CREATE TABLEsyntax. PostgreSQL does however create an index for unique constraints and primary keys by default, as described in this note:
似乎没有任何方法可以在CREATE TABLE语法中指定索引。然而，PostgreSQL 默认为唯一约束和主键创建索引，如本注释所述：
PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness.
PostgreSQL 自动为每个唯一约束和主键约束创建索引以强制唯一性。
Other than that, if you want a non-unique index, you will need to create it yourself in a separate CREATE INDEXquery.
除此之外，如果您想要一个非唯一索引，则需要在单独的CREATE INDEX查询中自己创建它。
回答by Bohemian
No.
不。
However, you can create uniqueindexes in the create, but that's because they are classed as constraints. You can't create a "general" index.
但是，您可以在创建中创建unique索引，但这是因为它们被归类为约束。您不能创建“通用”索引。
回答by Lukasz Szozda
Peter Krauss is looking for a canonical answer:
There are a MODERN SYNTAX (year 2020), so please explain and show examples, compatible with postgresql.org/docs/current/sql-createtable.html
彼得克劳斯正在寻找一个规范的答案：
有一个 MODERN SYNTAX (year 2020)，所以请解释并展示例子，兼容 postgresql.org/docs/current/sql-createtable.html
You are searching for inline index definition, which is not available for PostgreSQL up to current version 12. Except UNIQUE/PRIMARY KEY constraint, that creates underlying index for you.
您正在搜索内联索引定义，它不适用于 PostgreSQL 直到当前版本 12。除了 UNIQUE/PRIMARY KEY 约束，它为您创建基础索引。
[ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters |
[CONSTRAINTconstraint_name] {检查（表达式）[NO INHERIT] | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters |
The sample syntax of inline column definition(here SQL Server):
内联列定义的示例语法（此处为 SQL Server）：
CREATE TABLE tab(
id INT PRIMARY KEY, -- constraint
c INT INDEX filtered (c) WHERE c > 10, -- filtered index
b VARCHAR(10) NOT NULL INDEX idx_tab_b, -- index on column
d VARCHAR(20) NOT NULL,
INDEX my_index NONCLUSTERED(d) -- index on column as separate entry
);
The rationale behind introducing them is quite interesting What are Inline Indexes? by Phil Factor
引入它们背后的基本原理非常有趣什么是内联索引？菲尔因素
