如何将这些邮政编码导入规范化表格？ [英] How to import these postal codes into a normalized table?

问题描述

我有一个包含以下数据的CSV：

I've got a CSV with some data that looks like this:

A0A0A0,48.5674500000,-54.8432250000,Gander,NL
A0A1A0,47.0073470000,-52.9589210000,Aquaforte,NL
A0A1B0,47.3622800000,-53.2939930000,Avondale,NL

但是我的数据库已经标准化，因此城市和省份位于不同的表格中，每个表格都有自己的ID列。

But my database is normalized such that Cities and Provinces are in separate tables, each with their own ID column.

那么最简单的方法是什么将此文件导入3个单独的表并正确链接外键？

So what's the easiest way to import this file into 3 separate tables and link the foreign keys properly?

更清楚的是，表格是

cities (id, name, province_id)
provinces (id, code, name, country_id)
postal_codes (id, code, city_id)
countries (id, code, name)

推荐答案

使用 COPY 将csv导入临时表。比使用一些 INSERT INTO ... SELECT ... FROM ... 将数据转储到正确的表中。

Use COPY to import the csv into a temp table. Than use some INSERT INTO ... SELECT ... FROM ... to dump the data in the correct tables.

这篇关于如何将这些邮政编码导入规范化表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！