'trying to import json data on PostgreSQL
I am trying to import data on PostgreSQL
in my PostgreSQL table column includes
ojbectId (text)
createdAt (timestamptz)
updatedAt (timestamptz)
radius (float8)
pos (jsonb)
theta (float8)
cpId (text)
type (float8)
isRestricted (float8)
and my test json file includes
{
"ptest": [
{
"objectId" : "abc",
"createdAt" : "2022-05-06T02:34:21.229Z",
"updatedAt" : "2022-05-06T02:34:21.229Z",
"radius" : 1.0,
"theta" : 180.0,
"attributes" : "{}",
"cpId" : "abcd",
"name" : "{\"en\": \"\", \"kr\": \"\", \"en-US\": \"\", \"ko-KR\": \"\"}",
"pos" : "{\"x\": 62.35, \"y\": 25.1}",
"isRestricted" : 0.0,
"type" : 600.0
},
{
"objectId" : "def",
"createdAt" : "2022-05-06T02:34:38.311Z",
"updatedAt" : "2022-05-06T02:34:38.311Z",
"radius" : 1.0,
"theta" : 180.0,
"attributes" : "{}",
"cpId" : "efgh",
"name" : "{\"en\": \"\", \"kr\": \"\", \"en-US\": \"\", \"ko-KR\": \"\"}",
"pos" : "{\"x\": 33.81, \"y\": 16.9}",
"isRestricted" : 0.0,
"type" : 600.0
},
I tried to import this json file into PSQL command by
cat temp.json | psql -h localhost -p 5432 -U postgres -c "COPY ptest (objectId, createdAt, updatedAt, radius, theta, attributes, cpid, name, pos, isRestricted, type) FROM STDIN;"
from those json file, I directly parsed my column names, but the error occurs with "cloumn objectId" of relation "ptest" does not exist
I tried to find multiple exmaples for importing psql data with json. https://www.crunchydata.com/blog/fast-csv-and-json-ingestion-in-postgresql-with-copy
only this link was closest with my command, and I also couldn't solve this problem
How should I fix my psql command to input data?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
