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
1105
votes
20 answers

Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
Hoff
  • 38,776
  • 17
  • 74
  • 99
776
votes
21 answers

How to import CSV file data into a PostgreSQL table

How can I write a stored procedure that imports data from a CSV file and populates the table?
vardhan
  • 7,769
  • 3
  • 16
  • 3
487
votes
14 answers

How to export table as CSV with headings on Postgresql?

I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings. My code looks as follows: COPY products_273 to '/tmp/products_199.csv' delimiters',';
Elitmiar
  • 35,072
  • 73
  • 180
  • 229
284
votes
12 answers

Export specific rows from a PostgreSQL table as INSERT SQL script

I have a database schema named: nyummy and a table named cimory: create table nyummy.cimory ( id numeric(10,0) not null, name character varying(60) not null, city character varying(50) not null, CONSTRAINT cimory_pkey PRIMARY KEY (id) ); I…
null
  • 8,669
  • 16
  • 68
  • 98
114
votes
6 answers

How to copy from CSV file to PostgreSQL table with headers in CSV file?

I want to copy a CSV file to a Postgres table. There are about 100 columns in this table, so I do not want to rewrite them if I don't have to. I am using the \copy table from 'table.csv' delimiter ',' csv; command but without a table created I get…
Soatl
  • 10,224
  • 28
  • 95
  • 153
56
votes
3 answers

How does COPY work and why is it so much faster than INSERT?

Today I spent my day improving the performance of my Python script which pushes data into my Postgres database. I was previously inserting records as such: query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)"; for d in data: …
turnip
  • 2,246
  • 5
  • 30
  • 58
56
votes
3 answers

In PostgreSQL, how to insert data with COPY command?

I have problem when run 1 project NodeJs with PostgreSQL database. I have error when trying to insert data in pgAdmin using the COPY command. COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin; Bons Voeux blonde 9.5…
user4646310
37
votes
7 answers

How to export table data from PostgreSQL (pgAdmin) to CSV file?

I am using pgAdmin version 4.3 and i want to export one table data to CSV file. I used this query COPY (select * from product_template) TO 'D:\Product_template_Output.csv' DELIMITER ',' CSV HEADER; but it shows error a relative path is not allowed…
Dhouha
  • 661
  • 1
  • 8
  • 27
32
votes
8 answers

copy data from csv to postgresql using python

I am on windows 7 64 bit. I have a csv file 'data.csv'. I want to import data to a postgresql table 'temp_unicommerce_status' via a python script. My Script is: import psycopg2 conn = psycopg2.connect("host='localhost' port='5432' dbname='Ekodev'…
Manish Gupta
  • 4,438
  • 18
  • 57
  • 104
31
votes
4 answers

ERROR: missing data for column when using \copy in psql

I'm trying to import a .txt file into PostgreSQL. The txt file has 6 columns: Laboratory_Name Laboratory_ID Facility ZIP_Code City State And 213 rows. I'm trying to use \copy to put the contents of this file into a table called doe2 in…
nathanmgroom
  • 417
  • 1
  • 5
  • 6
29
votes
2 answers

How to generate a schema from a CSV for a PostgreSQL Copy

Given a CSV with several dozen or more columns, how can a 'schema' be created that can be used in a CREATE TABLE SQL expression in PostgreSQL for use with the COPY tool? I see plenty of examples for the COPY tool, and basic CREATE TABLE expressions,…
DPSSpatial
  • 767
  • 3
  • 11
  • 31
28
votes
6 answers

PostgreSQL: export resulting data from SQL query to Excel/CSV

I need to export the resulting data from a query in PostgreSQL to Excel/CSV. I use PostgreSQL 8.2.11. SQL error: ERROR: relative path not allowed for COPY to file In statement: COPY (select distinct(m_price) from m_product)TO…
Ghostman
  • 6,042
  • 9
  • 34
  • 53
26
votes
8 answers

ERROR: could not stat file "XX.csv": Unknown error

I run this command: COPY XXX FROM 'D:/XXX.csv' WITH (FORMAT CSV, HEADER TRUE, NULL 'NULL') In Windows 7, it successfully imports CSV files of less than 1GB. If the file is more then 1GB big, I get an “unknown error”. [Code: 0, SQL State: XX000] …
亚军吴
  • 391
  • 1
  • 3
  • 7
19
votes
3 answers

Importing zipped CSV file into PostgreSQL

I have a big compressed csv file (25gb) and I want to import it into PostgreSQL 9.5 version. Is there any fast way to import zip or qzip file into postgres without extracting the file?
Arezoo
  • 452
  • 1
  • 5
  • 15
14
votes
5 answers

ERROR: COPY delimiter must be a single one-byte character

I want to load the data from a flat file with delimiter "~,~" into a PostgreSQL table. I have tried it as below but looks like there is a restriction for the delimiter. If COPY statement doesn't allow multiple chars for delimiter, is there any…
vchitta
  • 2,043
  • 9
  • 28
  • 37
1
2 3
…
9 10