sql - 使用 COPY FROM 从 CSV 导入数据时转换列
问题描述
使用 PostgreSQL 12,我尝试从 CSV 导入数据,格式如下:
country,state,county,lat,lng,type,measure,beds,population,year,source,source_url
US,AK,ketchikan gateway,63.588753,-154.493062,ICU,1000HAB,3.928701,13745,2018,arcgis,https://services1.arcgis.com/Hp6G80Pky0om7QvQ/arcgis/rest/services/Hospitals_1/FeatureServer/0
US,AK,kodiak island,63.588753,-154.493062,ACUTE,1000HAB,,n,2018,arcgis,https://services1.arcgis.com/Hp6G80Pky0om7QvQ/arcgis/rest/services/Hospitals_1/FeatureServer/0
请注意,第二行的“人口”字段有一个n
而不是空的。我的目标是导入 CSV,以便“人口”列是 BIGINT,并且“n”被替换为 NULL。我目前的解决方案是:
CREATE TABLE temp_table
(
country CHAR(2),
state CHAR(2),
county VARCHAR(255),
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
type VARCHAR(11),
measure VARCHAR(255),
beds DOUBLE PRECISION,
pop VARCHAR(255),
year SMALLINT,
source VARCHAR(255),
source_url VARCHAR(255)
);
COPY temp_table
FROM 'C:\\Users\\mconr\\Downloads\\global-hospital-beds-capacity-for-covid19\\hospital_beds_USA_v1.CSV'
WITH (DELIMITER ',', FORMAT CSV, HEADER TRUE);
SELECT country, state, county, lat, lng, type, measure, beds, CAST (NULLIF (pop, 'n') AS BIGINT) AS population, year, source, source_url
INTO USA
FROM temp_table;
DROP TABLE temp_table;
我当前的解决方案是创建一个临时表,其中“population”为 VARCHAR(255),导入数据,从 SELECT 语句创建一个新表,将“n”替换为 NULL 并将列转换为 BIGINT,然后删除临时表. 但是,这似乎有点低效,因为我正在创建和删除一个中间表。有谁知道这样做的更好方法?
解决方案
您可以将 NULL 参数设置为“n”,但这会破坏其他情况,其中空字段以通常的方式(由未引用的空字符串)表示,而不是由“n”表示。COPY 不允许您为每列设置 NULL。
您当前的方法对我来说似乎很好,这将是我的首选(除了修复可能超出您控制范围的此文件的生成器)。您还可以使用 Perl 或 awk 或 sed 编写一些内容,将文件作为流编辑以将“,n”转换为“,”,并使用 PROGRAM 功能将其连接到 COPY,但这似乎很繁琐且错误很容易,我怀疑效率提升会那么大。
这是最邪恶的根源的那些过早的优化之一。这真的是一个有意义的问题吗?
推荐阅读
- performance - 如果你使用 defstruct slot 选项 `:type` 那么你会消除运行时检查吗?
- javascript - 是否可以在返回的 promise 对象中访问和打印 'value' 的值
- android - 错误:“类型不匹配:推断类型为单位,但预期为布尔值”请帮我解决下面的这个错误,我也给出了代码
- sql - 检查数组字段是空还是空?
- api - 假人的 API 请求:对 Spotify API 的 POST 请求收到错误
- python - Flask Chrome SameSite=无混淆
- python - 为什么 kivy 会忽略我的 config.set 并以默认分辨率打开?
- react-native-render-html - Balise的显示就像在 react-native-render-html 自定义渲染器中一样
- r - 使用 ifelse() 语句获取 R 中不同类的对象
- python - Django查看聚合不做计算