gpt4 book ai didi

postgresql - 将 Postgres 主键类型从 varchar 更改为 uuid 并被其他表引用

转载 作者:行者123 更新时间:2023-11-29 11:25:52 26 4
gpt4 key购买 nike

我有表客户和用户。我想将他们的 id 从 character varying 更改为 uuid。

=>\d customers;
Table "public.customers"
Column | Type | Modifiers
----------+-------------------+-----------
id | character varying | not null
name | character varying | not null
Indexes:
"customers_pkey" PRIMARY KEY, btree (id)
"customers_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "users" CONSTRAINT "users_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED

=>\d users;
Table "public.users"
Column | Type | Modifiers
---------------+-------------------+-----------
id | character varying | not null
name | character varying | not null
customer_id | character varying | not null
login | character varying | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_login_key" UNIQUE CONSTRAINT, btree (login)
Foreign-key constraints:
"users_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED

我试过:

=>begin;
set constraints all deferred;
ALTER TABLE users alter customer_id type uuid using customer_id::uuid;
ALTER TABLE customers alter id type uuid using id::uuid;
end;

但是在这一行之后我得到了不兼容的类型错误

=> ALTER TABLE customers alter id type uuid using id::uuid;
ERROR: foreign key constraint "users_customer_id_fkey" cannot be implemented
DETAIL: Key columns "customer_id" and "id" are of incompatible types: character varying and uuid.

最佳答案

您肯定想删除外键约束。我很惊讶您不必为 PostgreSQL 删除 PK 约束或索引来更改数据类型。我设置了一个测试,它运行良好,只需要先删除 FK,然后再重新添加它。

关于postgresql - 将 Postgres 主键类型从 varchar 更改为 uuid 并被其他表引用,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33441438/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com