CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-keys
David Planella
David Planella

Reputation: 2353

How to populate a table's foreign keys from other tables

I've got the following tables, of which translation is empty and I'm trying to fill:

translation {
    id
    translated
    language_id
    template_id
}

language {
    id
    langname
    langcode
}

template {
    id
    tplname
    source
    domain
    total
}

The source data to fill translation is a temporary table that I've populated from an external CSV file:

tmp_table {
    id
    translated
    langname
    tplname
    source
    domain
}

What I'd like to do is to fill translation with the values from tmp_table. The translated field 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).

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 translation table. Any ideas?

Upvotes: 6

Views: 18689

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656566

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 BY clause that you don't strictly need, but certain queries may profit if you insert your data clustered like that (or some other way).

If you want to avoid losing rows where you can't find a matching row in language or template, make it LEFT JOIN instead of JOIN for both tables (provided that language_id and template_id can be NULL.

In addition to what I already listed under your previous question: If the INSERT is huge and constitutes a large proportion of the target table, it is probably faster to drop all indexes on the target table and recreate them afterwards. Creating indexes from scratch is a lot faster then updating them incrementally for every row.

Unique indexes also serve as constraints, so you'll have to consider whether to enforce the rules later or leave them in place.

Upvotes: 6

Christopher Crooker
Christopher Crooker

Reputation: 194

I'm not as familiar with PostgreSQL as other RDBMS but it should be something like:

   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).

Upvotes: 1

Andrew Logvinov
Andrew Logvinov

Reputation: 21831

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;

Upvotes: 1

Related Questions