在PostgreSQL中通过获取序列的NextVal将数据插入表中

Question

我有一张名为SEQ_TABLE的表格，其中包含两列，SEQ_NAME和ID。

SEQ_NAME        |  ID
----------------------
SEQ_TABLE_10    |   1
SEQ_TABLE_20    |   5

在TABLE_10和TABLE_20中，取COLUMN_1的最大值作为ID。

现在，我需要从SEQ_TABLE获取序列的nextvalue，并将新记录插入到TABLE_10中。

我编写了以下PostgreSQL查询：

INSERT INTO TABLE_10 (COLUMN_1, COLUMN_2, COLUMN_3) VALUES  ((SELECT nextval(SEQ_TABLE)),   'Bangalore' ,NULL);

当我执行上述查询时，会出现以下错误： ********** 错误 **********

ERROR: column "SEQ_TABLE_10" does not exist
SQL state: 42703
Character: 99

但是，在 MySQL 数据库中，以下查询可以正常工作：

INSERT INTO TABLE_TABLE(COLUMN_1, COLUMN_2, COLUMN_3) VALUES  ((SELECT nextval('TABLE_172_SEQ','true')),    'Bangalore' ,NULL);

在PostgreSQL数据库中，要实现这个功能，需要使用什么确切的Postgres查询？

- Satish

---

MySQL不像Postgres一样支持序列。这意味着你在MySQL中使用的nextval函数可能不是MySQL内置的函数。请检查一下你的MySQL数据库中是否有任何名为nextval的用户定义函数？如果找到该函数，你可以将该函数复制到Postgres数据库中并使用它，否则最好按照@apm的答案创建序列。 - Manish

你可能误解了我的评论。我的意思是MySQL没有序列(sequence)，因此无法使用nextval函数。但是你在MySQL中使用了nextval函数调用。我怀疑你在MySQL中使用的nextval函数可能是一个用户自定义函数。请检查一下你的MySQL数据库中是否有名为"nextVal"的用户自定义函数。 - Manish

在您的MySQL语句中使用SELECT nextval('TABLE_172_SEQ','true')表示您有一个用户定义的nextval函数，该函数接受两个参数，第一个参数是table_name，它是SEQ_TABLE中的列值。 - Manish

如果该值为null，您可以使用IS NULL运算符将其增加1。但我认为您可能不想这样做。对于您的情况，为了迁移到Postgres，您可能只想在Postgres中使用与MySQL相同的用户定义的nextval函数。只需将相同的函数复制到Postgres，并进行任何必要的语法更改。然后，与MySQL相同的调用也应在Postgres中起作用。 - Manish

@Manish：没错。您能给我提供查询语句，以获取列中的值，并将其加1，即使它的值为null吗？ - Satish

Answer 1

您想创建一个序列，而不是一张表（SEQ_TABLE）。请参考此链接：https://www.postgresql.org/docs/8.1/static/sql-createsequence.html。

例如：

CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
create table MyTable(col1 int,col2 varchar(20));
INSERT INTO MyTable VALUES (nextval('serial'), 'nothing');

- apm

Answer 2

在当前的PostgreSQL版本中，您可以使用identity列：

CREATE TABLE table_10 (
   id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   other text NOT NULL
);

这将隐式生成一个序列。您不允许在id中插入数字，但是您必须

INSERT INTO table_10 (other) VALUES ('new');

或者

INSERT INTO table_10 (id, other) VALUES (DEFAULT, 'new');

在新版本的PostgreSQL中，您可以使用serial或bigserial来自动填充id列的下一个值。

---

在旧版本的PostgreSQL中，您将使用serial或bigserial：

CREATE TABLE table_10 (id serial PRIMARY KEY, other text NOT NULL);

那么id的类型是integer，一个序列table_10_id_seq被创建，并且在id列的DEFAULT子句中添加了一个nextval调用。

您可以使用\d在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)

此外，该序列属于表，并且在删除表时将自动删除。

您可以如下省略id列插入行：

INSERT INTO table_10 (other) VALUES ('something');

那么将使用DEFAULT默认值。

如果您想手动完成同样的操作，例如使用不同的名称或不同的起始值来生成序列，可以按照以下步骤进行：

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');

- Laurenz Albe