[SOLVED] Is it possible to have a CSV header when exporting table to file/stdout directly in SQL query and not in post-processing step?

Is it possible to have a CSV header when exporting table to file/stdout directly in SQL query and not in post-processing step?

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.

Solution

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]}"