How to use \copy in postgresql with pgadmin4

Question

I want to use the \copy command to make csv file with relative path. I used a query to make from test table to csv file named test.csv

\copy (SELECT * FROM test) to './test.csv' with csv

But in postgresql pgadmin4, it shows that \copy command as a syntax error (there is an underline under the word '\copy') and shows a message like below.

ERROR:  syntax error at or near "/"
LINE 2: /copy (SELECT * FROM test) to './persons_client.csv' with cs...
        ^
********** Error **********

ERROR: syntax error at or near "/"
SQL state: 42601
Character: 2

How can I solve this problem?

Erwin Brandstetter · Accepted Answer · 2022-12-27 07:42:46Z

11

\copy is a meta-command of the default command-line interface psql. You cannot run it from the "Query Tool" of pgAdmin4 (or any other SQL client). Run it from psql instead. (You can open a "PSQL tool" in modern pgAdmin4.)

psql's \copy is a client-side wrapper for the SQL-command COPY. If you have access to the database server (and the necessary privileges) you may be able to use SQL COPY instead.

edited Dec 27, 2022 at 7:42

answered Dec 17, 2016 at 6:49

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CompEng88 Over a year ago

"You can open a "PSQL tool" in modern pgAdmin4" how about mentioning how?

Erik van Velzen Over a year ago

To enable the PSQL tool in pgadmin4 you need to set ENABLE_PSQL=True in config.py or PGADMIN_CONFIG_ENABLE_PSQL=True in environment variables. A button wil then be visible to the right of "Object Explorer". See pgadmin.org/docs/pgadmin4/development/psql_tool.html

abautista · Accepted Answer · 2018-02-05 00:33:37Z

5

What I did to solve this problem was to execute:

psql=# copy tmp from '/path/to/file.csv' with delimiter ',' csv header encoding 'windows-1251';

answered Feb 5, 2018 at 0:33

abautista

2,8309 gold badges47 silver badges87 bronze badges

Comments

hanmari · Accepted Answer · 2024-08-06 00:01:39Z

In the pgadmin 4 interface there is a button for Query Tool and a separate button for PSQL Tool at the top of the object explorer. When running a COPY command they will behave differently.

Here is an example query that can be executed within the Query Tool:

SELECT id, username, firstname, lastname FROM users;

The Query Tool will display the result in the Data Output tab and you will need to click on the Save Results to File button. This means that you actually run the query twice: once to view, and again to save. If the result set is very large it will take a long time and may time out during the save process.

The PSQL Tool, however, mimics a terminal console and can write directly to a file immediately, which will save you the extra view step and avoid the timeout issue from the Query Tool. This is the command you would execute from within the PSQL Tool:

\COPY (SELECT id, username, firstname, lastname FROM users)
TO '/Users/MyUsername/Downloads/users.csv'
DELIMITER ',' CSV HEADER;

Please note that you will need to supply a relevant path for your system.

Collectives™ on Stack Overflow

How to use \copy in postgresql with pgadmin4

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related