SQL 将列的数据类型更改为串行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16474720/
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
Alter data type of a column to serial
提问by 1252748
In pgsql, is there a way to have a table of several values, and choose one of them (say, other_id), find out what its highest value is and make every new entry that is put in the table increment from that value.
在 pgsql 中，有没有办法拥有一个包含多个值的表，然后选择其中一个（比如 other_id），找出它的最大值是什么，并使放入表中的每个新条目从该值开始递增。
I suppose this was just too easy to have had a chance of working..
我想这太容易了，没有机会工作..
ALTER TABLE address ALTER COLUMN new_id TYPE SERIAL
____________________________________
ERROR: type "serial" does not exist
Thanks much for any insight!
非常感谢您的任何见解！
采纳答案by leonbloy
A quick glance at the docstells you that
快速浏览一下文档就会告诉你
The data types smallserial, serialand bigserial are not true typesbut merely a notational convenience for creating unique identifier columns
数据类型 smallserial、serial和 bigserial不是真正的类型，而仅仅是创建唯一标识符列的符号方便
If you want to make an existing (integer) column to work as a "serial", just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current address.new_idvalue, at set it as default value for your address.new_idcolumn.
如果您想让现有（整数）列作为“序列”工作，只需手动创建序列（名称是任意的），将其当前值设置为当前值的最大值（或更大）address.new_id，在 set它作为您的address.new_id列的默认值。
To set the value of your sequence see here.
要设置序列的值，请参见此处。
SELECT setval('address_new_id_seq', 10000);
SELECT setval('address_new_id_seq', 10000);
This is just an example, use your own sequence name (arbitrary, you create it), and a number greater than the maximum current value of your column.
这只是一个例子，使用您自己的序列名称（任意，您创建它），以及一个大于您的列的最大当前值的数字。
Update:as pointed out by Lucas' answer (which should be the acccepted one) you should also specify to which column the sequence "belongs to" by using CREATE/ALTER SEQUENCE ... OWNED BY ...
更新：正如卢卡斯的回答（应该是公认的）所指出的，您还应该通过使用指定序列“属于”哪一列CREATE/ALTER SEQUENCE ... OWNED BY ...
回答by Lucas
Look into postgresql documentation of datatype serial. Serial is only short hand.
查看数据类型 serial 的postgresql 文档。串行只是简写。
CREATE TABLE tablename (
colname SERIAL
);
is equivalent to specifying:
相当于指定：
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
回答by Sergey Novopoltsev
This happened because you may use serialdata type only when you are creating new table or adding new column to a table. If you'll try to ALTER existing table using this data type you'll get an error. Because serialis not a true data type, but merely an abbreviation or alias for a bit longer query.
发生这种情况是因为您只能在创建新表或向表中添加新列时使用串行数据类型。如果您尝试使用此数据类型更改现有表，则会出现错误。因为串行不是真正的数据类型，而只是更长查询的缩写或别名。
In case you would like to reach the same effect, as you are expecting from using serial data type when you are altering existing table you may do this:
如果您想达到相同的效果，正如您期望在更改现有表时使用串行数据类型，您可以这样做：
CREATE SEQUENCE my_serial AS integer START 1 OWNED BY address.new_id;
ALTER TABLE address ALTER COLUMN new_id SET DEFAULT nextval('my_serial');
First line of query creates your own sequence with name my_serial. OWEND BYstatement connects newly created sequence with exact column of your table. In your exact case table is addressand column is new_id. STARTstatement defines what value should this sequence start from.
Second line alters your table with new default value, which will be determined by previously created sequence.
第一行查询创建您自己的序列，名称为my_serial。 OWEND BY语句将新创建的序列与表的确切列连接起来。在您的确切情况下，表是地址，列是new_id。 START语句定义此序列应从哪个值开始。
第二行使用新的默认值更改您的表格，这将由之前创建的序列确定。
It will brings you to the same result as you were expecting from using serial.
它将为您带来与您期望使用串行相同的结果。
