How to export data from postgresql database table to excel format (recommended)

In the previous article, I introduced it to youHow to export data from postgresql database table to excel format (recommended)Interested friends click to view.

This article is mainly used to introduce how to use the copy or \copy command to export the data of the table in the postgresql database to excel format, so that users can view and edit.

The copy command is syntax the same as the \copy command. The difference is that copy must be used by super users. The files in copy … to file are files on the server where the database server is located, while \copy can be executed by ordinary users and the files saved or read by \copy are on the server where the client is located. This article mainly focuses on the introduction of the copy command. Using the copy command to invert the data in the table into a csv format file, that is, it is in Excel format.
1. Copy command syntax

COPY { Table name [ ( Column name [, ...] ) ] | ( Query ) } 
TO { 'file name' | PROGRAM 'Order' | STDOUT } 
 [ [ WITH ] ( Options [, ...] ) ]

Options可以是下列内容之一
 FORMAT Format_name 
 FREEZE [ Boole ] 
 DELIMITER 'Separating Characters' 
 NULL 'Empty String' 
 HEADER [ Boole ] 
 QUOTE 'Quoting Characters' 
 ESCAPE 'Escape Character' 
 FORCE_QUOTE { ( Column name [, ...] ) | * } 
 FORCE_NOT_NULL ( Column name [, ...] ) 
 FORCE_NULL ( Column name [, ...] ) 
 ENCODING 'encoding_name(Code name)'

2. Introduction to the use of multiple scenarios
①View existing table data

test=# select * from test;
user_id | user_name | age | gender |     remark     
---------+---------------+-----+--------+----------------------------------------------
  1 | * | 45 | male | "police story","project A","rush hour"
  3 | Brigitte Li | 46 | female | 
  4 | Maggie Cheung | 39 | female | 
  5 | Jet Li  | 41 | male | "Fist of Legend","Once Upon a Time in China"
  2 | Gong Li  | 38 | female | "Farewell My Concubine","Lifetimes Living"
(5 Line records)

②Export with column names, used by default as a separator

test=# copy test to '/tmp/' with csv header;
COPY 5
test=# \! cat /tmp/
user_id,user_name,age,gender,remark
1,*,45,male,"""police story"",""project A"",""rush hour"""
3,Brigitte Li,46,female,
4,Maggie Cheung,39,female,
5,Jet Li,41,male,"""Fist of Legend"",""Once Upon a Time in China"""
2,Gong Li,38,female,"""Farewell My Concubine"",""Lifetimes Living"

③Export with column names, specify the use of | as a separator

test=# copy test to '/tmp/' with csv header DELIMITER '|';
COPY 5
test=# \! cat /tmp/
user_id|user_name|age|gender|remark
1|*|45|male|"""police story"",""project A"",""rush hour"""
3|Brigitte Li|46|female|
4|Maggie Cheung|39|female|
5|Jet Li|41|male|"""Fist of Legend"",""Once Upon a Time in China"""
2|Gong Li|38|female|"""Farewell My Concubine"",""Lifetimes Living"

④ Export with column name, replace the empty character with the specified value export

test=# copy test to '/tmp/' with csv header null 'to be supplemented';
COPY 5
test=# \! cat /tmp/
user_id,user_name,age,gender,remark
1,*,45,male,"""police story"",""project A"",""rush hour"""
3,Brigitte Li,46,female,to be supplemented
4,Maggie Cheung,39,female,to be supplemented
5,Jet Li,41,male,"""Fist of Legend"",""Once Upon a Time in China"""
2,Gong Li,38,female,"""Farewell My Concubine"",""Lifetimes Living"

This is the article about how to export data from postgresql database tables into excel format. For more related content on exporting data from postgresq tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!