我在Postgres 10.10中有下表：
Table "public.client"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+------------------------------------------
clientid | integer | | not null | nextval('client_clientid_seq'::regclass)
account_name | text | | not null |
last_name | text | | |
first_name | text | | |
address | text | | not null |
suburbid | integer | | |
cityid | integer | | |
post_code | integer | | not null |
business_phone | text | | |
home_phone | text | | |
mobile_phone | text | | |
alternative_phone | text | | |
email | text | | |
quote_detailsid | integer | | |
invoice_typeid | integer | | |
payment_typeid | integer | | |
job_typeid | integer | | |
communicationid | integer | | |
accessid | integer | | |
difficulty_levelid | integer | | |
current_lawn_price | numeric | | |
square_meters | numeric | | |
note | text | | |
client_statusid | integer | | |
reason_for_statusid | integer | | |
Indexes:
"client_pkey" PRIMARY KEY, btree (clientid)
"account_name_check" UNIQUE CONSTRAINT, btree (account_name)
Foreign-key constraints:
"client_accessid_fkey" FOREIGN KEY (accessid) REFERENCES access(accessid)
"client_cityid_fkey" FOREIGN KEY (cityid) REFERENCES city(cityid)
"client_client_statusid_fkey" FOREIGN KEY (client_statusid) REFERENCES client_status(client_statusid)
"client_communicationid_fkey" FOREIGN KEY (communicationid) REFERENCES communication(communicationid)
"client_difficulty_levelid_fkey" FOREIGN KEY (difficulty_levelid) REFERENCES difficulty_level(difficulty_levelid)
"client_invoice_typeid_fkey" FOREIGN KEY (invoice_typeid) REFERENCES invoice_type(invoice_typeid)
"client_job_typeid_fkey" FOREIGN KEY (job_typeid) REFERENCES job_type(job_typeid)
"client_payment_typeid_fkey" FOREIGN KEY (payment_typeid) REFERENCES payment_type(payment_typeid)
"client_quote_detailsid_fkey" FOREIGN KEY (quote_detailsid) REFERENCES quote_details(quote_detailsid)
"client_reason_for_statusid_fkey" FOREIGN KEY (reason_for_statusid) REFERENCES reason_for_status(reason_for_statusid)
"client_suburbid_fkey" FOREIGN KEY (suburbid) REFERENCES suburb(suburbid)
Referenced by:
TABLE "work" CONSTRAINT "work_clientid_fkey" FOREIGN KEY (clientid) REFERENCES client(clientid)
我想将clientid从串行 ID (nextval('client_clientid_seq'::regclass)( 更改为not null generated always as identity primary key。
该表有 107 条手动输入的记录，包括客户端 ID。
如何在不破坏现有数据的情况下做到这一点？
BEGIN;
ALTER TABLE public.client ALTER clientid DROP DEFAULT; -- drop default
DROP SEQUENCE public.client_clientid_seq; -- drop owned sequence
ALTER TABLE public.client
-- ALTER clientid SET DATA TYPE int, -- not needed: already int
ALTER clientid ADD GENERATED ALWAYS AS IDENTITY (RESTART 108);
COMMIT;
有两个变量：
- 附加
SEQUENCE的实际名称。我使用了上面的默认名称，但名称可能会有所不同。 - 当前最大值(以
client.clientid为单位(。不必是 107，只是因为当前有 107 行。
此查询同时获取：
SELECT pg_get_serial_sequence('client', 'clientid'), max(clientid) FROM client;
serial列是拥有专用序列的integer列，其默认设置是从中绘制的(从您发布的表定义中可以看出(。要使其成为普通integer，请删除默认值，然后删除序列。
将列转换为IDENTITY会添加其自己的序列。您必须删除旧的拥有序列(或至少是所有权，所有权会随着删除序列而死亡(。否则，您会收到以下错误：
ERROR: more than one owned sequence found
- 如何复制表格的结构和内容，但使用单独的顺序？
然后将纯integer列转换为IDENTITY列，并使用当前最大值加 1重新启动。必须设置新内部序列的当前值以避免唯一冲突。
将所有内容包装在单个事务中，这样您就不会在迁移过程中搞砸。所有这些 DDL 命令在 Postgres 中都是事务性的，可以在提交之前回滚，并且仅对之后开始的其他事务可见。
您的列之前是PK的，并且保持PK。这与更改正交。
Peter Eisentraut，(Postgres 10中的新功能(IDENTITY功能的主要作者，也提供了一个函数upgrade_serial_to_identity()来转换现有的serial列。它重用现有序列，而是直接更新系统目录 - 除非您确切知道自己在做什么，否则您不应该自己执行此操作。它还涵盖了异国情调的极端情况。查看一下("升级"一章(：
- https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/
但是，该函数不适用于不允许直接操作系统目录的大多数托管服务。然后，您将按照顶部的说明返回到 DDL 命令。
相关：
如何将主键从整数转换为串行？
自动递增表列
如何复制表的结构和内容，但使用单独的顺序？
您可以更改定义，语法为：
ALTER TABLE table_name
ALTER COLUMN column_name
{ SET GENERATED { ALWAYS| BY DEFAULT } |
SET sequence_option | RESTART [ [ WITH ] restart ] }
不确定是否需要先使用SET DEFAULT NULL更改列。并确保序列超过手动插入的值，以免发生冲突。
我最终使用setval而不是ALTER与RESTART的值，如果序列重新启动，则无法使用语句动态提供。解决方案是先设置列默认值，然后再设置序列值。
ALTER TABLE schema.table ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE schema.sequence_name;
ALTER TABLE schema.table ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT pg_catalog.setval(
pg_get_serial_sequence('schema.table', 'id'),
COALESCE((SELECT MAX(id) + 1 FROM schema.table), 1),
false
);
几点注意事项：
- 用于正确处理空表
COALESCE MAX(id) + 1+ 第 3 个参数falsesetval用于防止序列中的间隙，同时具有最小的1值- 利用
pg_get_serial_sequence()使新生成的序列的名称是动态的，并允许更改命名方案
我对麦地那发布的脚本进行了一些更改，使其能够为我工作
SELECT 'ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' DROP DEFAULT;
'||replace('DROP SEQUENCE '''||substring(column_default, 9, length(column_default)-19), '''', '')||';
ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' ADD GENERATED ALWAYS AS IDENTITY;
SELECT SETVAL(pg_get_serial_sequence('''||table_schema||'.'||TABLE_NAME||''', '''||COLUMN_NAME||'''),
(SELECT COALESCE(MAX('||COLUMN_NAME||'), 0) + 1 FROM '||table_schema||'.'||TABLE_NAME||'), false);'
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';
SELECT 'ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' DROP DEFAULT;
'||replace('DROP SEQUENCE '''||substring(column_default, 9, length(column_default)-19), '''', '')||' CASCADE;
ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER COLUMN '||COLUMN_NAME||' set not null;
ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval(pg_get_serial_sequence(''"'||TABLE_NAME||'"'', '''||COLUMN_NAME||'''),
(select max('||COLUMN_NAME||') from '||table_schema||'."'||TABLE_NAME||'"));'
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';
此查询的结果可帮助您将所有串行 ID 替换为生成的标识