前端之家

  1. 首页
  2. 问答

如何将表ID从序列号更改为标识号？

• 问答

我在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从序列号（nextval('client_clientid_seq'::regclass)）更改为not null generated always as identity primary key。

该表有107条记录，其中包括手动输入的客户ID。

如何在不破坏现有数据的情况下完成此操作？

rhrhok 回答：如何将表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

How to copy structure and contents of a table,but with separate sequence?

然后将普通的integer列转换为IDENTITY列，并以当前的最大加1 重新启动。您必须设置新内部序列的当前值，以避免出现唯一冲突。

将其全部包装在一个事务中，因此您不会在迁移过程中半途而废。所有这些DDL命令在Postgres中都是事务性的，可以回滚直到提交，并且此后才对其他事务可见。

您的色谱柱之前为PK，现在保持为PK。这与变化正交。

（Postgres 10中的新增功能）IDENTITY功能的主要作者彼得·艾森特拉特（Peter Eisentraut）还提供了函数upgrade_serial_to_identity() 来转换现有的serial列。它重用了现有序列，而是直接更新系统目录-除非您确切知道自己在做什么，否则您不应该自己做。它还涵盖了异国情调的极端案例。签出（“升级”一章）：

但是，该功能不适用于不允许直接操纵系统目录的大多数托管服务。然后，您将按照顶部的说明返回DDL命令。

相关：

,

您可以更改定义，语法为：

ALTER TABLE table_name 
ALTER COLUMN column_name 
{ SET GENERATED { ALWAYS| BY DEFAULT } | 
  SET sequence_option | RESTART [ [ WITH ] restart ] }

不确定是否需要先使用SET DEFAULT NULL更改列。并确保该序列超出了手动插入的值，因此没有冲突。

, 
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%';

此查询的结果可帮助您将所有序列号替换为生成的身份

auto-incrementddlpostgresqlsql
本文链接：https://www.f2er.com/2955041.html

大家都在问