Insert data in Table using a pre-specified json Postgresql

Question

I have a geojson pre-specified as data in PostgreSQL pgAdmin4 query tool.I am trying to insert data into table but as my JSON is large so instead of manually doing Insert into I do this:

WITH data AS (SELECT '{ "type": "FeatureCollection",
"features": [
  { "type": "Feature",
    "geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
    "properties": {"prop0": "value0"}
    },
  { "type": "Feature",
    "geometry": {
      "type": "LineString",
      "coordinates": [
        [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
        ]
      },
    "properties": {
      "prop0": "value0",
      "prop1": 0.0
      }
    }
   ]
 }'::json AS fc)

Now trying to insert this data into table like this:

INSERT INTO locations (gid, geom, properties) VALUES ( data );

It gives me an error that column data doesn't exist.But when I do this:

SELECT
row_number() OVER () AS gid,
ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom,
feat->'properties' AS properties
FROM (
 SELECT json_array_elements(fc->'features') AS feat
FROM data
 ) AS f;

This query shows data. My issue is that when I want I can select and see data but as this is not stores anywhere so everytime I need to do WITH data as declare json and apply the select query. So I want to inset value in to my table so I can call this anytime.

Also see https://stackoverflow.com/q/3306096/1965736 – philngo Feb 12 '18 at 06:22 — philngo, Feb 12 '18 at 06:22

score 1 · Accepted Answer · answered Feb 12 '18 at 06:21

You should be able to directly use your SELECT in place of the VALUES statement, like so:

WITH data AS (SELECT '{ "type": "FeatureCollection",
"features": [
  { "type": "Feature",
    "geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
    "properties": {"prop0": "value0"}
    },
  { "type": "Feature",
    "geometry": {
      "type": "LineString",
      "coordinates": [
        [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
        ]
      },
    "properties": {
      "prop0": "value0",
      "prop1": 0.0
      }
    }
   ]
 }'::json AS fc)
INSERT INTO locations (gid, geom, properties)
SELECT
row_number() OVER () AS gid,
ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom,
feat->'properties' AS properties
FROM (
 SELECT json_array_elements(fc->'features') AS feat
FROM data
 ) AS f;

Insert data in Table using a pre-specified json Postgresql

1 Answers1