sql - 为什么 postgres 表在使用 json 数据执行 sql insert 查询后每列都显示 Null?
问题描述
我正在尝试使用此查询将 json 数据插入到 postgres 表中-
INSERT INTO rf_dsgns
SELECT * FROM json_populate_recordset(NULL::rf_dsgns,
'[
{
"Tracking_ID": 2377125,
"Constr_Zone": "Cleveland",
"AF_Name": "PbCleveland_10236716P",
"Address": "4755 1/2 Rose Avenue",
"Zip_Code": 44867,
"Latitude": 5.8923486,
"Longitude": -71.71052258,
},{
"Tracking_ID": 2377126,
"Constr_Zone": "Cleveland",
"AF_Name": "PggClevelandCLE_25236718P",
"Street_Address": "4413 1/3 Clain Avenue",
"Zip_Code": 44225,
"Latitude": 40.88960254,
"Longitude": -71.20898567,
}]');
我在创建表时使用的数据类型分别是整数、字符、字符、字符、整数、数字、数字。我的创建表脚本是
CREATE TABLE rf_dsgns
(
tracking_id integer,
constr_zone character(300),
af_name character(300),
address character(300),
zip_code integer,
latitude numeric,
longitude numeric
);
解决方案
您很可能在不使用双引号作为列名的情况下创建表(这是一件好事)。但是,json_populate_recordset()
匹配区分大小写,因此表中的小写列名称与 JSON 中的混合大小写名称不匹配。
这个:
create table rf_dsgns ("Tracking_ID" int, "Constr_Zone" text, "AF_Name" text, "Address" text, "Zip_Code" text, "Latitude" numeric, "Longitude" numeric);
SELECT *
FROM json_populate_recordset(NULL::rf_dsgns,
'[
{
"Tracking_ID": 2377125,
"Constr_Zone": "Cleveland",
"AF_Name": "PbCleveland_10236716P",
"Address": "4755 1/2 Rose Avenue",
"Zip_Code": 44867,
"Latitude": 5.8923486,
"Longitude": -71.71052258
},{
"Tracking_ID": 2377126,
"Constr_Zone": "Cleveland",
"AF_Name": "PggClevelandCLE_25236718P",
"Street_Address": "4413 1/3 Clain Avenue",
"Zip_Code": 44225,
"Latitude": 40.88960254,
"Longitude": -71.20898567
}]');
返回:
Tracking_ID | Constr_Zone | AF_Name | Address | Zip_Code | Latitude | Longitude
------------+-------------+---------------------------+----------------------+----------+-------------+-------------
2377125 | Cleveland | PbCleveland_10236716P | 4755 1/2 Rose Avenue | 44867 | 5.8923486 | -71.71052258
2377126 | Cleveland | PggClevelandCLE_25236718P | | 44225 | 40.88960254 | -71.20898567
但是,当创建不带引号的表时:
create table rf_dsgns (tracking_id int, constr_zone text, af_name text, address text, zip_code text, latitude numeric, longitude numeric);
然后没有列匹配,一切都将是NULL
我不会使用区分大小写的列名重新创建表。我可以看到两种解决方法:创建一个使用带引号的标识符但与表定义相同的新类型,并使用它来映射 JSON 数据。
改为使用json_array_elements()
并手动拼出所有列名。这需要更多的输入,但不会重复类型定义(实际上更灵活一些):
insert into rf_dsgns
SELECT (j ->> 'Tracking_ID')::int,
j ->> 'Constr_Zone',
j ->> 'AF_Name',
j ->> 'Addres',
j ->> 'Zip_Code',
(j ->> 'Latitude')::numeric,
(j ->> 'longitude')::numeric
FROM json_array_elements('.... your json here ') a t(j);
推荐阅读
- html - 如何在图像正下方对齐文本?
- python-3.x - Keras 的数据重塑不使用 K-Fold 验证
- homebrew - Mac os 10.9 中的 Gnu Cobol
- angular - 角度部署 - 部署具有语义版本的模块
- azure - 如何从工件 UI 下载通用工件
- ruby-on-rails - Rails 6 SQL 方法的语法
- shell - grep - 匹配前 n 行的字符串
- c# - 删除成绩单数据 C# 中的时间模式
- javascript - Javascript chrome 扩展在新标签页中不起作用
- python - 如何阻止熊猫按字母顺序对数据框中的列进行自动排序?