admin管理员组

文章数量:1417554

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 edited Jan 31 at 10:21 DarkBee 15.5k8 gold badges72 silver badges118 bronze badges asked Jan 31 at 9:09 rbaleksandarrbaleksandar 9,75111 gold badges94 silver badges182 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

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

本文标签：

版权声明：本文标题：postgresql - Is it possible to have a CSV header when exporting table to filestdout directly in SQL query and not in post-proces 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745273135a2651018.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。