Postgresql 用名称替换 ID 的最简单方法

Question

In Postgresql：

假设我有一张桌子Dogs：

SELECT * FROM "Dogs"
...
Name        OwnerID
'Pooch'     'OWR14'
'Sparky'    'OWR12'
'Pooch'     'OWR17'

还有一张表Owners：

SELECT * FROM "Owners"
...
ID         Name
'OWR14'    'Peter'
'OWR12'    'Jack'
'OWR17'    'Mary'

将OwnerID字段替换为Owner表中的Name值的最快/最简单的方法是什么？

我最快的方法：

SELECT "Dogs"."Name", "Owners"."Name" AS "Owner"
FROM "Dogs"
LEFT JOIN "Owners" ON "Dogs"."OwnerID" = "Owners"."ID"
...
Name        Owner
'Pooch'     'Peter'
'Sparky'    'Jack'
'Pooch'     'Mary'

Answer 1

查找您的样本可能是带有连接的更新

UPDATE Dogs
SET Dogs.OwnerID = Owners.Name
FROM Owners
WHERE Dogs.OwnerID = Owners.id;

否则，如果您只需要更快的查询，请确保在表 Dogs 上有适当的复合索引

CREATE INDEX my_idx1 on "Dogs" ("OwnerID", "Name" )

和表所有者

CREATE INDEX my_idx2 on "Owners" ("ID", "Name" )