postgresql 如何从其他表填充表的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9447506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to populate a table's foreign keys from other tables
提问by David Planella
I've got the following tables, of which translationis empty and I'm trying to fill:
我有以下表格，其中translation是空的，我正在尝试填写：
translation {
id
translated
language_id
template_id
}
language {
id
langname
langcode
}
template {
id
tplname
source
domain
total
}
The source data to fill translationis a temporary table that I've populated from an external CSV file:
要填充的源数据translation是我从外部 CSV 文件填充的临时表：
tmp_table {
id
translated
langname
tplname
source
domain
}
What I'd like to do is to fill translationwith the values from tmp_table. The translatedfield can be copied directly, but I'm not quite sure how to fetch the right language_id(tmp_table.langname could be used to determine language.id) and template_id(tmp_table.tplname, tmp_table.source, tmp_table.domain together can be used to determine template.id).
我想要做的是填补translation与价值观tmp_table。该translated字段可以直接复制，但是我不太清楚如何取正确的language_id（tmp_table.langname可以用来确定language.id）和template_id（tmp_table.tplname、tmp_table.source、tmp_table.domain一起可以用来确定模板.id）。
It might be a trivial question, but I'm quite new to SQL and not sure what the best query should be to populate the translationtable. Any ideas?
这可能是一个微不足道的问题，但我对 SQL 很陌生，不确定填充translation表的最佳查询应该是什么。有任何想法吗？
采纳答案by Erwin Brandstetter
This can be simplified to:
这可以简化为：
INSERT INTO translation (id, translated, language_id, template_id)
SELECT tmp.id, tmp.translated, l.id, t.id
FROM tmp_table tmp
JOIN language l USING (langname)
JOIN template t USING (tplname, source, domain)
ORDER BY tmp.id
I added an ORDER BYclause that you don't strictly need, but certain queries may profit if you insert your data clustered that (or some other) way.
我添加了一个ORDER BY您并不严格需要的子句，但是如果您插入以这种（或其他）方式聚集的数据，某些查询可能会受益。
If you want to avoid losing rows where you can't find a matching row in languageor template, make it LEFT JOINinstead of JOINfor both tables (provided that language_idand template_idcan be NULL.
如果您想避免丢失在language或 中找不到匹配行的行template，请使用它LEFT JOIN代替JOIN两个表（前提是language_id并且template_id可以是NULL.
In addition to what I already listed under the prequel question: If the INSERT is huge and constitutes a large proportion of the target table, it is probably faster to DROP all indexeson the target table and recreate them afterwards. Creating indexes from scratch is a lotfaster then updating them incrementally for every row.
除了我在前传问题下已经列出的内容：如果 INSERT 很大并且占目标表的很大一部分，那么在目标表上删除所有索引并在之后重新创建它们可能会更快。从头开始创建索引比为每一行增量更新索引要快得多。
Unique indexes additionally serve as constraints, so you'll have to consider whether to enforce the rules later or leave them in place.
唯一索引还用作约束，因此您必须考虑是稍后强制执行规则还是保留它们。
回答by Andrew Logvinov
insert into translation (id, translated, language_id, template_id)
select tmp.id, tmp.translated, l.id, t.id
from tmp_table tmp, language l, template t
where l.langname = tmp.langname
and t.tplname = tmp.tplname
and t.source = tmp.source
and t.domain = tmp.domain;
回答by Christopher Crooker
I'm not as familiar with PostgreSQL as other RDBMS but it should be something like:
我不像其他 RDBMS 那样熟悉 PostgreSQL，但它应该是这样的：
INSERT INTO translation
SELECT s.id, s.translated, l.id, t.id FROM tmp_table s
INNER JOIN language l ON (l.langname = s.langname)
INNER JOIN template t ON (t.tplname = s.tplname)
Looks like someone just posted basically the same answer with slightly different syntax, but keep in mind: If there is no matching langname or tplname in the joined tables the rows from tmp_table will not get inserted at all and this will not make sure you don't create duplicates of translation.id (so make sure you don't run it more than once).
看起来有人刚刚发布了基本相同的答案，但语法略有不同，但请记住：如果连接表中没有匹配的 langname 或 tplname，则 tmp_table 中的行根本不会被插入，这将无法确保您不插入“ t 创建 translation.id 的副本（因此请确保不要多次运行它）。
