我正在尝试在PostgreSQL中运行以下INSERT代码。我有一些Json数组对象。当我将它们设置为null时,它可以工作,但是当我尝试使用json数组执行代码时,出现错误。我在下面描述细节:
我的插入SQL代码:
INSERT INTO vuln
(pde,
"pde_id",
pde_description,
pde_published,
pde_published_date,
pde_valid,
pde_cwe,
pde_references,
pde_vu_products,
pde_eval_has_ex,
pde_vu_products_has_inc_excl,
pde_impact)
VALUES ( '{"data_type": "PDE", "data_format": "MITRE", "data_version": "4.0", "PDE_data_meta": {"ID": "PDE-0001", "ASSIGNER": "[email protected]"}, "problemtype": {"problemtype_data": [{"description": [{"lang": "en", "value": "CWE-20"}]}]}, "references": {"reference_data": [{"url": "http://www.example.com ", "name": "http://www.example.com ", "refsource": "CONFIRM", "tags": []}, {"url": "http://www.example.com", "name": "5707", "refsource": "OSVDB", "tags": []}]}, "description": {"description_data": [{"lang": "en", "value": "asd"}]}}' , 'PDE-0001', 'asd', 'True', '1999-12-30T05:00Z', 'False', 'CWE-20', '[{"url": "http://www.example.com", "name": "http://www.example.com", "refsource": "CONFIRM", "tags": []}, {"url": "http://www.example.com", "name": "5707", "refsource": "OSVDB", "tags": []}]' , NULL, 'False', 'False', '{"baseMetricV2": {"cvssV2": {"version": "2.0", "vectorString": "AV:N/AC:L/Au:N/C:N/I:N/A:P", "accessVector": "NETWORK", "accessComplexity": "LOW", "authentication": "NONE", "confidentialityImpact": "NONE", "integrityImpact": "NONE", "availabilityImpact": "PARTIAL", "baseScore": 5.0}, "severity": "MEDIUM", "exploitabilityScore": 10.0, "impactScore": 2.9, "obtainAllPrivilege": false, "obtainUserPrivilege": false, "obtainOtherPrivilege": false, "userInteractionRequired": false}}' );
我收到以下错误:
LINE 4: '[{"url": "http://www.example.com...
^
DETAIL: Auf »[« müssen explizit angegebene Array-Dimensionen folgen.
我的表架构:
CREATE TABLE public.vu
(
"ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
PDE json,
"PDE_ID" text COLLATE pg_catalog."default",
PDE_published boolean,
PDE_references json[],
PDE_impact json,
PDE_cwe text COLLATE pg_catalog."default",
PDE_valid boolean,
PDE_published_date date,
PDE_eval_has_ex boolean,
PDE_vu_products_has_inc_excl boolean,
PDE_vu_products json[],
PDE_description text COLLATE pg_catalog."default",
CONSTRAINT vu_pkey PRIMARY KEY ("ID")
)
我究竟做错了什么?我试图使用json_array_elements()转换数组对象,但是它没有帮助我。
您尝试插入一个JSON在发动机预计数组的JSON(这不是一个JSON数组!)。您的列pde_references
定义为json[]
。尝试将值插入为具有单个值的数组:
INSERT INTO public.vu
(...
pde_references,
...)
VALUES (...
ARRAY['[{"url": "http://www.example.com", "name": "http://www.example.com", "refsource": "CONFIRM", "tags": []}, {"url": "http://www.example.com", "name": "5707", "refsource": "OSVDB", "tags": []}]']::json[]
...);
顺便说一下,布尔值周围的引号不是必需的。你可以只写true
或false
不'true'
和'false'
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句