如何将表ID从序列号更改为标识号? [英] How to change a table ID from serial to identity?
问题描述
我在Postgres 10.10中有下表:
I have the following table in 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始终作为身份主键生成.
I would like to change clientid from a serial id (nextval('client_clientid_seq'::regclass)) to not null generated always as identity primary key.
该表具有107条记录，这些记录是手动输入的，包括客户ID.
The table has 107 records which were manually entered including clientids.
如何在不破坏现有数据的情况下完成此操作?
How could this be done without destroying existing data?
推荐答案
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行.
- the actual name of the attached
SEQUENCE. I used the default name above, but the name can differ. - the current maximum value in
client.clientid. Doesn't have to be 107, just because there are currently 107 rows.
此查询同时获得:
SELECT pg_get_serial_sequence('client', 'clientid'), max(clientid) FROM client;
serial 列是拥有专用序列的 integer 列，其默认设置可从中提取(如所见)从您发布的表定义中).要使其成为普通的 integer ，请删除默认值，然后删除序列.
A serial column is an integer column that owns a dedicated sequence and has its default set to draw from it (as can be seen from the table definition you posted). To make it a plain integer, drop the default and then drop the sequence.
将列转换为 IDENTITY 会添加其自己的序列.您必须删除旧拥有的序列(或至少所有权，因为删除该序列而死亡).否则，您会收到类似以下错误:
Converting the column to an IDENTITY adds its own sequence. You must drop the old owned sequence (or at least the ownership, which dies with dropping the sequence). Else you get errors like:
ERROR: more than one owned sequence found
然后将普通的 integer 列转换为 IDENTITY 列，并以当前的最大加1 重新启动.您必须设置新内部序列的当前值，以避免出现唯一冲突.
Then convert the plain integer column to an IDENTITY column, and restart with the current maximum plus 1. You must set the current value of the new internal sequence to avoid unique violations.
将其全部包装在一个事务中，因此您不会在迁移过程中半途而废.所有这些DDL命令在Postgres中都是事务性的，可以回滚直到提交，并且此后才对其他事务可见.
Wrap it all in a single transaction, so you don't mess up half way into the migration. All of these DDL commands are transactional in Postgres, can be rolled back until committed and are only visible to other transactions starting after that.
您的色谱柱之前为PK，现在保持PK.这与变化正交.
Your column was PK before and stays PK. This is orthogonal to the change.
(Postgres 10中的新功能) IDENTITY 功能的主要作者Peter Eisentraut还提供了函数 upgrade_serial_to_identity() 现有的 serial 列.它重用了现有序列，而是直接更新系统目录-除非您确切地知道自己在做什么，否则您不应该自己做.它还涵盖了异国情调的极端案例.签出(升级"一章):
Peter Eisentraut, the principal author of the (new in Postgres 10) IDENTITY feature, also provided a function upgrade_serial_to_identity() to convert existing serial columns. It reuses the existing sequence and instead updates system catalogs directly - which you should not do yourself unless you know exactly what you are doing. It also covers exotic corner cases. Check it out (chapter "Upgrading"):
但是，该功能不适用于不允许直接操纵系统目录的大多数托管服务.然后，您将按照顶部的说明返回DDL命令.
However, the function won't work on most hosted services that do not allow direct manipulation of system catalogs. Then you are back to DDL commands as instructed at the top.
相关:
