Extract data from postgres using bulk loading

Question

CMD.EXE /C BCP "SELECT * FROM ci.trn_td_cash" queryout D:\Sample\data\Load\CASH.COPY -c -t"|" -r"\n" -S xxx.xxx.xxx.xx -d finance -U user -P 1111 .

I want to extract data from postgresql using bulk loading BCP command.Can i use BCP for posgresql. I use the following command for bulk loading. But it is not connecting to database.

please help me with syntax.

[BCP](https://learn.microsoft.com/en-us/sql/tools/bcp-utility) is the SQL Server utility — cha, Apr 06 '18 at 05:26
So how i could extract data from posgresql using BCP.. Is there any other way to achieve it... — Bommu, Apr 06 '18 at 05:29
you need to use psql command [COPY](https://www.postgresql.org/docs/9.2/static/sql-copy.html) — cha, Apr 06 '18 at 05:30

Nishant Gupta · Answer 1 · 2018-04-09T04:06:06.140

0

COPY Command in PostgreSQL:

The COPY command allows high-speed bulk data transfer to or from the server. Copy-in and copy-out operations each switch the connection into a distinct sub-protocol, which lasts until the operation is completed.

COPY command is supported in PostgreSQL Protocol v3.0 (Postgresql 7.4 or newer).

For more info follow the below link:

GENERAL SYNTAX:

COPY (<select-query-here>) TO <file-path>;

Here is one answer I found to your question of how to run it:

https://ieftimov.com/postgresql-copy

Try this One:

\copy (note the backslash) lets you copy to/from remote databases and does not require superuser privileges.

psql -h remotehost -d remote_mydb -U 
myuser -c "\copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

edited Apr 09 '18 at 04:06

answered Apr 06 '18 at 05:33

Nishant Gupta

3,533
1
11
18

Thanks for the links.. but how could i execute it in command prompt. The server is remote so what changes i have to make in command. please help. – Bommu Apr 06 '18 at 05:58
@Bommu: `copy` is a SQL statement, you need to run a SQL client like `psql` and connect to the remote server – Apr 06 '18 at 06:12
Above link shows how to insert into postgres from csv not how to extarct from postgres. – Bommu Apr 06 '18 at 07:00
@Bommu I changed the link have a look. – Nishant Gupta Apr 06 '18 at 08:35
@Nishat Gupta... thanks for the link.. Do you have any idea on what changes do i need make in syntax if i have to execute in command prompt and the database is remote with username and password... – Bommu Apr 09 '18 at 01:50

Extract data from postgres using bulk loading

1 Answers1