postgresql 在PostgreSQL中通过获取序列的NextVal插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38389619/
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
Insert into Table by getting NextVal of Sequence in PostgreSQL
提问by Satish
I have a table called SEQ_TABLE which has two columns, SEQ_NAME and ID
我有一个名为 SEQ_TABLE 的表，它有两列，SEQ_NAME 和 ID
SEQ_NAME | ID
----------------------
SEQ_TABLE_10 | 1
SEQ_TABLE_20 | 5
Where ID is the Max of COLUMN_1 of TABLE_10 and TABLE_20
其中 ID 是 TABLE_10 和 TABLE_20 的 COLUMN_1 的最大值
Now, I have to Insert new records into TABLE_10 by obtaining nextvalueof sequence from SEQ_TABLE.
现在，我通过获取新记录插入TABLE_10 nextvalue从序列SEQ_TABLE。
I have written PostgreSQL query as follows:
我编写的 PostgreSQL 查询如下：
INSERT INTO TABLE_10 (COLUMN_1, COLUMN_2, COLUMN_3) VALUES ((SELECT nextval(SEQ_TABLE)), 'Bangalore' ,NULL);
When I execute above Query, It is giving below error: ********** Error **********
当我执行上面的查询时，它给出了以下错误：********** 错误 **********
ERROR: column "SEQ_TABLE_10" does not exist
SQL state: 42703
Character: 99
But, following Query works fine in MySQLDatabase:
但是，以下查询在MySQL数据库中工作正常：
INSERT INTO TABLE_TABLE(COLUMN_1, COLUMN_2, COLUMN_3) VALUES ((SELECT nextval('TABLE_172_SEQ','true')), 'Bangalore' ,NULL);
What is the Exact Postgres Query to achieve it in PostgreSQLDB?
在PostgreSQLDB 中实现它的确切 Postgres 查询是什么？
回答by apm
You want to create a sequence, not a table(SEQ_TABLE) Kindly refer this link https://www.postgresql.org/docs/8.1/static/sql-createsequence.html
你想创建一个序列，而不是一个表（SEQ_TABLE）请参考这个链接 https://www.postgresql.org/docs/8.1/static/sql-createsequence.html
Eg.
例如。
CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
create table MyTable(col1 int,col2 varchar(20));
INSERT INTO MyTable VALUES (nextval('serial'), 'nothing');
回答by Laurenz Albe
In PostgreSQL you would use
在 PostgreSQL 中，您将使用
CREATE TABLE table_10 (id serial PRIMARY KEY, other text NOT NULL);
Then idis of type integer, a sequence table_10_id_seqis created and a nextvalcall is added to the DEFAULTclause of the idcolumn.
然后id是 type integer，一个序列table_10_id_seq被创建，一个nextval调用被添加到列的DEFAULT子句中id。
You can examine this with \din psql:
您可以使用\din进行检查psql：
\d table_10
Table "laurenz.table_10"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
id | integer | not null default nextval('table_10_id_seq'::regclass)
other | text | not null
Indexes:
"table_10_pkey" PRIMARY KEY, btree (id)
Moreover, the column belongs to the table and will be dropped automatically when the table is dropped.
而且，该列属于表，在删除表时会自动删除。
You insert rows omitting the idcolumn like this:
你插入行省略id列是这样的：
INSERT INTO table_10 (other) VALUES ('something');
Then the DEFAULTvalues will be used.
然后DEFAULT将使用这些值。
If you want to do the same thing “on foot”, for example to use a different name or different starting values for the sequence, that would work like this:
如果您想“步行”做同样的事情，例如为序列使用不同的名称或不同的起始值，可以这样工作：
CREATE TABLE table_10 (id integer PRIMARY KEY, other text NOT NULL);
CREATE SEQUENCE table_10_id_seq OWNED BY table_10.id;
ALTER TABLE table_10 ALTER id SET DEFAULT nextval('table_10_id_seq');
