generate json with PostgreSQL

Question

I have postgreSQL 8.4+PostGIS 1.5.

I want to generate GeoJson. I do:

SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
, ST_AsGeoJSON(lg.the_geom)::json As geometry
, row_to_json(lp) As properties
   FROM parcels_temp As lg 
     INNER JOIN (SELECT num, cadastr FROM parcels_temp) As lp 
   ON lg.num = lp.num  ) As f )  As fc;

But get an error:

 ERROR:  type "json" does not exist
 LINE 4:     , ST_AsGeoJSON(lg.the_geom)::json As geometry

What am I doing wrong?

The json datatype and the row_to_json function was introduced in 9.2 You will need to upgrade. — user330315
– user330315, Commented Jan 22, 2013 at 7:30

Craig Ringer · Accepted Answer · 2013-01-22 07:35:44Z

10

There is no json data type in PostgreSQL 8.4. The type was introduced in 9.2, though a backport to 9.1 was created; see this bitbucket.

Use text. json is just a validating wrapper around the text type anyway, the interesting bit is the functions like row_to_json - which are also unavailable for 8.4.

If you can't use text - say, because you're using 3rd party code that expects json, or because you need the json functions - then it's time to upgrade PostgreSQL. 8.4 is getting pretty elderly anyway, as is PostGIS 1.5.

answered Jan 22, 2013 at 7:35

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kliver Max Over a year ago

Thanks. It what i want to hear.

Collectives™ on Stack Overflow

generate json with PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related