I have a table that contains two columns (beside the serial ID)

• name - a VARCHAR that contains a label for the geometry
• geom - a GEOMETRY(POINT, 4326)

in my PostGIS DB. I use the following SQL query to create a CSV file:

COPY (
    SELECT name, ST_AsGeoJSON(geom)
    FROM public.points
)
TO STDOUT
WITH DELIMITER ',' CSV HEADER

The result is

name,st_asgeojson
POI1,"{""type"":""Point"",""coordinates"":[6.409522,48.015928]}"
POI2,"{""type"":""Point"",""coordinates"":[-70.982805,34.669752]}"

I need ST_AsGeoJSON() or similar function since otherwise the result will be a binary string that I then need to post-process (in my case using psycopg2).

Is there a way to create a custom header, so that I have the original table column name (in my case geom)?

Even though I use PostGIS (based on PostgreSQL) my question refers to any PostgreSQL CSV-to-file/stdout operation, since ST_AsGeoJSON() is just a function that processes the column's entries and one can define a custom one, leading to a similar outcome in the resulting CSV header.

I have a table that contains two columns (beside the serial ID)

• name - a VARCHAR that contains a label for the geometry
• geom - a GEOMETRY(POINT, 4326)

in my PostGIS DB. I use the following SQL query to create a CSV file:

COPY (
    SELECT name, ST_AsGeoJSON(geom)
    FROM public.points
)
TO STDOUT
WITH DELIMITER ',' CSV HEADER

The result is

name,st_asgeojson
POI1,"{""type"":""Point"",""coordinates"":[6.409522,48.015928]}"
POI2,"{""type"":""Point"",""coordinates"":[-70.982805,34.669752]}"

I need ST_AsGeoJSON() or similar function since otherwise the result will be a binary string that I then need to post-process (in my case using psycopg2).

Is there a way to create a custom header, so that I have the original table column name (in my case geom)?

Even though I use PostGIS (based on PostgreSQL) my question refers to any PostgreSQL CSV-to-file/stdout operation, since ST_AsGeoJSON() is just a function that processes the column's entries and one can define a custom one, leading to a similar outcome in the resulting CSV header.

• postgresql
• csv

Share Improve this question Follow edited Jan 31 at 10:21 DarkBee 15.5k88 gold badges7272 silver badges118118 bronze badges asked Jan 31 at 9:09 rbaleksandarrbaleksandar 9,7511111 gold badges9494 silver badges182182 bronze badges 1

• 2 I removed the <sql> tag since this is product specific, and not related to ISO/ANSI SQL. – jarlh Commented Jan 31 at 9:16

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It is evident that my SQL knowledge is quite rusty. I recalled I can use AS in a SELECT statement to rename columns:

COPY (
    SELECT name, ST_AsGeoJSON(geom) AS geom
    FROM public.points
)
TO STDOUT
WITH DELIMITER ',' CSV HEADER

will produce

name,geom
POI1,"{""type"":""Point"",""coordinates"":[6.409522,48.015928]}"
POI2,"{""type"":""Point"",""coordinates"":[-70.982805,34.669752]}"