将列的数据类型更改为串行 [英] Alter data type of a column to serial
问题描述
在pgsql中,有没有一种方法可以建立包含多个值的表,并选择其中一个值(例如other_id),找出其最大值,然后使表中每个新条目都从该值开始递增值。
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.
我想这太容易了,不可能有工作的机会。
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!
推荐答案
快速浏览在文档告诉您
数据类型smallserial, serial 和bigserial 不是真实类型
,但是仅仅是创建唯一标识符列的一种符号方便性
The data types smallserial, serial and bigserial are not true types but merely a notational convenience for creating unique identifier columns
t使现有(整数)列用作序列,只需手动创建序列(名称是任意的),并将其当前值设置为当前的最大值(或更大) address.new_id
值,将其设置为 address.new_id
列的默认值。
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_id
value, at set it as default value for your address.new_id
column.
要设置序列的值,请参见此处。
To set the value of your sequence see here.
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 ...
这篇关于将列的数据类型更改为串行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!