'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