Stack Overflow
Stack Overflow

Questions tagged [postgresql-copy]

COPY is an SQL command in PostgreSQL to move data between files and tables. There is also the meta-command \copy in the psql interface.

Besides the shell utilities pg_dump and pg_restore for backup and restore, there is also the SQL command COPY in PostgreSQL to move data between files and database tables quickly. Several file formats are supported: text, csv and binary.

The meta-command \copy in the psql interface is a wrapper for the SQL command that reads and writes files local to the client (while COPY is for files on the server).

Examples

Write all rows from a table employees to a CSV file on the DB server with SQL COPY:

COPY employees TO '/path/to/employees.csv' (FORMAT csv);

There are more examples for COPY in the manual .

Read data from a CSV file on the client machine into a table with matching structure with \copy in psql (appending to existing data):

\copy employees FROM '/path/to/employees.csv' (FORMAT csv);
133 questions
3
votes
2 answers

Problems while importing a txt file into postgres using php

I am trying to import a txt/csv file into my postgres database from php using "\copy" command. I cannot use COPY instead of \copy as I need it to execute as a psql client. My code is: $query = '\\'.'copy data1 FROM "data1.txt" WITH CSV HEADER…
Himanshu Shekhar
  • 397
  • 4
  • 15
3
votes
2 answers

PostgreSQL COPY command with csv file

I have a csv file with two columns: productId, productName. My table has the following columns: productId, productName, productprice. I want to be able to copy this file into a Postgres table using the COPY command and at the same time set a value…
NAC
  • 135
  • 2
  • 10
3
votes
2 answers

COPY csv file with additionnal datas

I have the following table : persons(id,id_tech,name,nationality,id_list) And a CSV file containing the datas for the column id_tech,name and nationality. Importing the data like so is working : \copy persons(id_tech,name,nationality) FROM…
grunk
  • 13,700
  • 12
  • 61
  • 103
3
votes
1 answer

Split a big postgres table into multiple csv

I am using following psql query to connect to a remote host and split a big table into multiple csv files. psql -h xx -p xx -U xx -d xx -c "\COPY (select * from table) TO program 'split --lines 1000' (format csv) I am not getting what mistake I am…
rshar
  • 737
  • 1
  • 12
3
votes
2 answers

How to use \copy in postgresql with pgadmin4

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…
윤성필
  • 65
  • 1
  • 1
  • 6
3
votes
2 answers

Alternative to "write to file" for transfering CSV data to PostgreSQL using COPY for better performance?

I have a dataset in a CSV file consisting of 2500 lines. The file is structured that (simplified) way: id_run; run_name; receptor1; receptor2; receptor3_value; [...]; receptor50_value Each receptor of the file is already in a table and have a unique…
kaycee
  • 811
  • 1
  • 6
  • 28
3
votes
2 answers

Using Postgres's COPY FROM file query in Python without writing to a temporary file

I need to load data from some source data sources to a Postgres database. To do this task, I first write the data to a temporary CSV file and then load data from the CSV file to Postgres database using COPY FROM query. I do all of this on…
user3422637
  • 3,369
  • 13
  • 39
  • 65
2
votes
1 answer

Issue with importing formatted numbers over 1,000 with COPY

I have a Postgres database and have been trying to import a CSV file into a table with the code below. I keep getting the error ERROR: invalid input syntax for type numeric: " 1,183.26 " I assume the issue is that there is a , in the value but…
Hercislife
  • 75
  • 1
  • 7
2
votes
2 answers

Is it possible to make the psql \copy see a line inside a csv file as a comment?

I'm successfully inserting csv files to postgresql with the following command: \COPY tablename(col1, col2, col3) FROM '/home/user/mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null'; However, I'd like to write some metadata inside this csv file…
Rafael Muynarsk
  • 412
  • 1
  • 6
  • 18
2
votes
2 answers

Using AWK and PostgreSQL's COPY FROM PROGRAM together

I have a .dat file consisting of thousands of lines, where each line is made of 4 sets of numbers, delimited by ::. It ends up looking something like this: 1234::482::4::1342.5321234 4342::532::1::1532.532431 I'm trying to copy this file into a…
paul go
  • 25
  • 5
2
votes
1 answer

Copy output of WITH subquery to CSV in postgres

I am trying to save the output of below 'WITH' sub-query to a csv file. WITH mer9 AS ( SELECT *, substring(seq_window_mut_9mers, split9.start, 9) FROM split9 ), mer23 AS ( …
rshar
  • 737
  • 1
  • 12
2
votes
1 answer

How PostgreSQL Copy command work on Windows?

I need to copy table data into text file on Windows in csv format. How to do that? What I have tried: COPY test TO '"E:\\test.csv"' DELIMITER ',' CSV HEADER; I am receiving an error while executing this query. That test.csv file have to create…
user2266543
  • 85
  • 7
2
votes
2 answers

How to force postgres to use columns order from csv when dumping/restore data?

I dump my table data: COPY( SELECT * FROM tariff_details ) TO STDOUT WITH( FORMAT CSV, HEADER ) The data: id,tariff_id,name,price,option,periodic,value,sorder 17,1,Setup fee,5.000000000000000000,,f,,0 When I restore the data: COPY tariff_details…
Eugen Konkov
  • 15,716
  • 7
  • 69
  • 107
2
votes
1 answer

Is there a way to use Postgresql copy (loading CSV in table) from Hibernate?

In my current application I am using Hibernate + PostgreSQL. For a particular case I need to use the COPY functionality available in postgres to load data from CSV file. Is there any way to use COPY using Hibernate. Postgres version : 9.4 Hibernate…
Ketu
  • 1,230
  • 2
  • 9
  • 24
2
votes
1 answer

Python PostgreSQL using copy_from to COPY list of objects to table

I'm using Python 2.7 and psycopg2 to connect to my DB server ( PostgreSQL 9.3 ) and I a list of objects of ( Product Class ) holds the items which i want to insert products_list =…
Morad Edwar
  • 910
  • 1
  • 9
  • 27
1 2
3
…
8 9