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?

Answer 1

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.