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);
147 questions
4
votes
2 answers

Postgres SQL - error: must be superuser to copy to or from a file

I have copied function(from one of the webportal and modified accordingly) to copy data from csv file to table. create or replace function public.load_csv_file ( target_table text, csv_path text, col_count integer ) returns void as…
Geeme
  • 395
  • 2
  • 6
  • 18
4
votes
1 answer

importing data with commas in numeric fields into redshift

I am importing data into redshift using the SQL COPY statement. The data has comma thousands separators in the numeric fields which the COPY statement rejects. The COPY statement has a number of options to specify field separators, date and time…
Daniel Mahler
  • 7,653
  • 5
  • 51
  • 90
4
votes
1 answer

How to convert hex characters when using Postgres COPY FROM?

I am importing data from a file to PostgreSQL database table using COPY FROM. Some of the strings in my file contain hex characters (mostly \x0d and \x0a) and I'd like them to be converted into regular text using COPY. My problem is that they are…
Harry
  • 181
  • 5
  • 13
4
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,967
  • 17
  • 49
  • 72
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
  • 437
  • 1
  • 5
  • 16
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
  • 11
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
  • 1,381
  • 10
  • 28
3
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
  • 1,381
  • 10
  • 28
3
votes
3 answers

Cloud SQL - PostgreSQL - how to import a .csv file?

I have a Cloud SQL instance running PostgreSQL, and a .csv file on my local computer. I created a user and a database. I logged in as that user, with that database and created a table that matches my .csv file. I have a .csv file, and an…
user10664542
  • 1,106
  • 1
  • 23
  • 43
3
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
  • 105
  • 2
  • 7
3
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
  • 22,193
  • 17
  • 108
  • 158
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
  • 901
  • 1
  • 9
  • 35
3
votes
1 answer

How to ignore duplicate keys while using copy in postgresql

I am using COPY table_name FROM STDIN to import data. It is very efficient, but if there's any violation of duplicate keys, the whole procedure will be stopped. Is there anyway to around this? why does not postgresql just give a warning and copy…
Pavan
  • 31
  • 1
  • 3
3
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,608
  • 2
  • 14
  • 30
3
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
  • 1,030
  • 2
  • 11
  • 27
1 2
3
…
9 10